Excel VBA, Font Color

In this article I will explain how you can change the font color of cells and ranges in Excel using VBA. I’ve also provided an example on how you can get the current font color used in a cell.

For more information about working with colors in VBA for Excel please see VBA Excel Colors.

You can download the code and file for this article here.

Jump To:


Basics:

The code below changes the color of the font in cell A1 to red:

Range("A1").Font.Color = ColorConstants.vbRed

The code below does the same using the color code associated with the color red:

Range("A1").Font.Color = 255

Before:

Excel, VBA, Font Color, Before
After:

Excel, VBA, Font Color, After


Example 1, Set Font Color:

In the example below when the user presses the run button a color dialog will open prompting the user to select a color. The color of the font in cell A1 will change to the selected color:

Private Sub btnRun_Click()
'The color code selected by the user
Dim intColorCode As Long
'displays the color dialog
Application.Dialogs(xlDialogEditColor).Show (56)
'gets the color code selected by the user
intColorCode = ThisWorkbook.Colors(56)
'changes the color of the font in cell A1
Range("A1").Font.Color = intColorCode
End Sub

The user presses the Run button to display the color dialog:

Excel, VBA, Before
The user selects a color from the color dialog:

Excel, VBA, Color Dialog
The color of the font in cell A1 is changed:

Excel, VBA, After
The line below displays the color dialog:

Application.Dialogs(xlDialogEditColor).Show (56)

The line below gets the code of the color selected by the user:

intColorCode = ThisWorkbook.Colors(56)

The line below changes the color of the font to the color selected by the user:

Range("A1").Font.Color = intColorCode

For more information about working with colors please see Excel VBA, Colors.


Example 2, Get Font Color:

In this example there is some text written in the first row. When the user presses the run button the color code of the fonts in row 1 are printed in row 2:

Excel VBA, Example 2
After changing the font color in row 1 and pressing the Run button:

Excel VBA, Example 2, After

The code used in this example can be seen below:

Private Sub btnRun_Click()
Dim i As Integer
'loops through the cells in row 1
For i = 1 To 5
'gets the color code used for the font in
'row 1 and prints it in row 2
Cells(2, i) = _
Range(Cells(1, i), Cells(1, i)).Font.Color
Next i
End Sub

For more information about working with colors please see Excel VBA, Colors.

You can download the code and file for this article here.

See also:

If you need assistance with your code, or you are looking for a VBA programmer to hire feel free to contact me. Also please visit my website www.software-solutions-online.com

One thought on “Excel VBA, Font Color”

  1. Wayne says:

    I am trying to toggle the font color in cell M4 from white to black.
    Then if the same macro button is clicked it will change the font back to white.
    This is for a multiplication-table test for students. I have two cells with random numbers between 2 and 12 that are multiplied. The answer is in cell M4 and I want a macro button, that when clicked, it will show the answer, or when clicked again, it will hide the answer (really just making the font white). Can you help?

Leave a Reply

Your email address will not be published. Required fields are marked *