How to use colors in Excel 2010 VBA code

If you want to use colors in your VBA macros e.g. to modify cell background color or to change the font color, then you have two options on how to specify the colors:

1. By choosing from one of the preset EXCEL colors (56 colors). You do this by specifying a color index.

2. You can set your own colors by using RGB (Red, Green and Blue) values.

Examples of using the preset colorindex property.
1. Set background color:

Worksheets(“Calendar”).Range(“A1:M1”).Interior.ColorIndex = 34

2. Set font color:

Worksheets(“Calendar”).Range(“A1:M1”).Font.ColorIndex = 34

3. The following VBA code demonstrate how to use colorindex property. It is output is the complete list of the color index palette. The image below is the output from that code.

If you want to try it, name a sheet in your workbook : ”ColorIndex” and then paste the code in a new module and run it.

Sub ShowColorIndex()

Dim i As Integer, j As Integer

For i = 1 To 4

For j = 1 To 14

Worksheets(“ColorIndex”).Cells(j, (i – 1) * 2 + 1).Value = (i – 1) * 14 + j

Worksheets(“ColorIndex”).Cells(j, i * 2).Interior.ColorIndex = (i – 1) * 14 + j

Next j

Next i

End Sub

Examples of using the color property:

Set  background:

Worksheets(“Calendar”).Range(“A1:M1”).Interior.Color = RGB(218,225,130)

Set font  color:

Worksheets(“Calendar”).Range(“A1:M1”).Font.Color = RGB(218,225,130)

How do you figure out the right RGB color combination? Well you can experiment with the color palette. The following post will show you how.
How to create a custom font color or fill color in Excel 2010