There are two ways of setting colors of Cell Backgrounds, Fonts or Borders in Excel. One of them is the VBA ColorIndex property and the other of them is the VBA Color property. But what is the difference between the two, and which is better! So let us explore…
The VBA ColorIndex property
The VBA ColorIndex property can be any value between 1 and 56, and represents a color defined within your currently selected color theme with the provided index.
As you can see in the image on the left this represents a variety of colors. The downside of using the ColorIndex is that it is dependent on the actually selected color theme and you are limited to selecting colors from the 56 color palette.
Sub PrintColorIndexTable() For i = 1 To 56 With Cells(Int((i - 1) / 8) + 1, (i - 1) Mod 8 + 1) .Interior.ColorIndex = i .Value = i End With Next i End Sub
VBA ColorIndex example
The VBA Color property
The VBA Color property is a more convenient way of defining the color of a Cell Interior, Border or Font. The Excel VBA Color property accepts Long values representing a RGB color. The easiest way to set this property is to use the VBA RGB function:
Example: Setting cell background to red
Range("A1").Interior.Color = RGB(255, 0, 0)
Example: Setting cell font to blue
Range("A1").Font.Color = RGB(0, 0, 255)
Example: Setting cell borders to
Range("A1").Borders.Color = RGB(255, 255, 0)
Using colors is easily achieved in Excel VBA. The Excel VBA ColorIndex property limits you however to 56 theme or default Excel colors and is rarely used in practice. The Excel VBA Color on the other side allows you to set any color to any Excel Cell property using the VBA RGB function.