excel vba colorindex

Excel VBA ColorIndex vs VBA Color property

1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
Loading...

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…
vba colorindex

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.

VBA ColorIndex table
VBA ColorIndex table
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.

If you want to print a similar ColorIndex table simply use the code below in any selected worksheet:

VBA ColorIndex example

VBA ColorIndex usage examples
VBA ColorIndex usage examples
On the left you can see several examples of setting the VBA ColorIndex to different Excel Range properties such as Interior (background), Font and Borders.

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

Example: Setting cell font to blue

Example: Setting cell borders to yellow

Summary

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.

Related Posts