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
• Example 1, Set Font Color
• Example 2, Get Font Color

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:

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:

The user selects a color from the color dialog:

The color of the font in cell A1 is changed:

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:

After changing the font color in row 1 and pressing the Run button:

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:

• Excel VBA, Colors
• VBA, Excel Font Formatting
• Excel VBA, Formatting Cells and Ranges Using the Macro Recorder

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