Table of Contents
There are three main methods for changing font color in VBA: setting the Font Color property of a Range object, using the VBA RGB function, and using the VBA ColorIndex property. To set the Font Color property, you can use the Range.Font.Color property with a numeric parameter that corresponds to the desired color. The RGB function can be used to set the font color by passing three parameters that represent the amount of red, green, and blue in the desired color. Finally, the ColorIndex property can be used to set the font color by passing a parameter that corresponds to a preset color in the ColorIndex palette.
You can use one of the following methods to change font color in Excel by using VBA:
Method 1: Change Font Color Using VBA Color Names
Sub ChangeColor()
Range("A1").Font.Color = vbRed
End Sub
This particular macro will change the font color in cell A1 to red.
Method 2: Change Font Color Using RGB Values
Sub ChangeColor()
Range("A1").Font.Color = RGB(255,0,0)
End Sub
This particular macro will change the font color in cell A1 to the color that corresponds to the RGB (red green blue) values of (255,0,0) which happens to be red.
Method 3: Change Font Color Using Hex Color Codes
Sub ChangeColor()
Range("A1").Font.Color = &H0000FF
End Sub
This particular macro will change the font color in cell A1 to the color that corresponds to the hex color code of #FF0000.
Note #1: You must type &H in front of the hex color code you want to use in order for VBA to recognize ait as a hex color code.
Note #2: Typically hex color codes are represented by six characters in which the first two represent the code for red, the middle two represent the code for green, and the last two represent the code for blue.
However, Excel swaps the first two values for the last two values. Thus, the hex color code for red is FF0000 but we must type 0000FF instead.
The following examples show how to use each method in practice.
Example 1: Change Font Color Using VBA Color Names
Suppose we have some text in cell A1 that currently has a font color of black:
Sub ChangeColor()
Range("A1").Font.Color = vbRed
End Sub
When we run this macro, we receive the following output:
Notice that the font color in cell A1 is now red.
Example 2: Change Font Color Using RGB Values
Suppose we have some text strings in the range A1:A5 that currently have a font color of black:
We can create the following macro to change the font color in cells A1:A5 to red:
Sub ChangeColor()
Range("A1:A5").Font.Color = RGB(255,0,0)
End Sub
When we run this macro, we receive the following output:
Notice that the font color in each of the cells in the range A1:A5 is now red.
Example 3: Change Font Color Using Hex Color Codes
Once again suppose we have some text strings in the range A1:A5 that currently have a font color of black:
We can create the following macro to change the font color in cells A1:A5 to red:
Sub ChangeColor()
Range("A1:A5").Font.Color = &H0000FF
End Sub
When we run this macro, we receive the following output:
Notice that the font color in each of the cells in the range A1:A5 is now red.
Note: You can find the complete documentation for the VBA Font.Color property .