Setting Excel Font color using VBA
In this tutorial, we will show you how to set a font color in Excel sheet cells, rows, and columns by using the range object’s Font property.
If you want to use the default color palette in Excel, then use the ColorIndex property with fifty-six colors (1-56 code).
The examples below show using both ways.
See the following sample Worksheet that we will apply colors:
An example of changing the cell font color
The example below sets the font color of a cell.
We will set the font color by default code of the color to the B5 cell. For that, we used ColorIndex property.
Code:
Sub Font_color()
Range("B5").Font.ColorIndex = 5
End Sub
Result:
Changing multiple cells’ color
We created a range of cells (B4 to C8) and applied the color by Selection as follows:
Sub Font_color()
Range("B4:C8").Select
Selection.Font.ColorIndex = 45
End Sub
Result:
Setting font color of a row by RGB color
We will change the font color of the 5th row in our sample sheet by using RGB color and the Range’e Color property:
Sub Font_color()
Range("5:5").Select
Selection.Font.Color = RGB(64, 128, 128)
End Sub
Result:
Changing color of multiple rows
The code below changes the text color of rows 2 to 4 and 7 to 9 by using Font Color property and using RGB value:
Code:
Sub Font_color()
Range("2:4, 7:9").Select
Selection.Font.Color = RGB(0, 255, 0)
End Sub
Result:
Changing font color of three columns range
In this example, we used the Select method on the range for selecting B to D columns and then applied font color by Color property:
Sub Font_color()
Range("B:D").Select
Selection.Font.Color = vbBlue
End Sub
Result:
