In this Blog post I cover all ways and tips for using color codes in your Excel VBA program. Discover how to store colors as constants, identify the code of any color and apply colors to any Excel object.

Understanding Color Codes

Every color is identified by a unique number. For example, 255 represents Red. 16774980 represent a specific cyan.

You can also convert any decimal number to its Hexadecimal representation (or Hex for short). For example, that cyan color number equals FFF744 in Hex representation (use any decimal to hex converter you find on the web to see how it works).

You can assign the color codes to any Color property of any object in either their decimal or hex representation. Precede the Hex value with the &H prefix (our cyan code will be set to: &HFFF744). For example, the background color of cell A1 can be set to be cyan in one of the following two options:

Range("A1").Interior.Color = &HFFF744

Range("A1").Interior.Color = 16774980

Tip: to clear the background (Fill) color of a range, assign it the value -4142. Luckily, we have an Excel enumeration for that, so you do not need to remember the specific code, just assign xlNone like that:

Range("A1").Interior.Color = xlNone

The RGB representation of a color

Another way to represent a color is by its unique combination of the Red, Green and Blue components, or RGB in short. Each component takes a value between 1 and 255.

Our cyan color above has a mix of 68 red, 247 green and 255 blue.

VBA offers an RGB function to convert a mix of RGB values to the decimal code of a color, making it useful to assign an RGB mix to any Color property. Let’s set the font color of cell A1:

Range("A1").Font.Color = RGB(68,247,255)

You can use Excel to choose a color you like from the color selection dialog box to see its RGB values.

In recent Excel versions the Hex code of the color is also presented. If not, you can use any RGB to Hex converter (such as this one) to find the Hex code of a color.

The twist of Hex color codes in Excel

As you noticed, a hex color code contains 6 characters. That is a construction of two characters for the R (Red) code in the mix, two characters for the G (Green) and two characters for the B (Blue), resulting in the RGB mix that also uniquely identifies a color.

You may also know that colors in Webpages are also usually represented in their hex representation, preceded by the hash (“#”) symbol. You would expect that #FFF744 will show the cyan on a webpage. You would be surprised to see a bright yellow instead.

The reason is that while the Hex representation of a color is constructed by joining the RGB codes for the Web, Excel constructs its color codes by joining the BGR codes. Yep, the R and the B are swapped.

So, if you want to see our beautiful cyan on your Webpage, swap the first “FF” with the last “44” in our code, resulting to: #44F7FF.

By the way, you might want to remember that swapping trick when you set specific colors for different controls when you design your User Forms. This will be valid to represent our cyan code in any color property: &HFF3399.

Setting color codes as constants

Colors are a great candidate for constants, as in many cases their value doesn’t change that often, yet we still want a consistent and readable VBA code.

Constants can take any value, in both Hex and decimal. For example:

Public Const DATE_PICKER_BG_COLOR = &HFF3399

Tip: as constants cannot be assigned functions, RGB() cannot be used here. A quick way to find the numeric value of any RGB set would be using the Immediate Window in the VBA Editor (CTRL+G). To print out the numeric value of RGB(68,247,255), type in the following statement in the Immediate Window and press Enter: ?RGB(68,247,255)

Color codes enumerations

Excel VBA maintains enumerations for a bunch of popular colors. This means that you don’t have the know the color code of these colors, just use the enumeration label for the color you want.

For example, vbRed enumerates 255 (the color code for red). Print the value of vbGreen in the immediate Window, what do you get?

The enumerated colors are:

vbBlack, vbWhite, vbCyan, vbBlue, vbYellow, vbRed, vbMagenta, vbGreen.

Setting the font color of cell A1 to yellow, was never easier than:

Range("A1").Font.Color = vbYellow

Using the ColorIndex property

In addition to the Color property used to set the color of some Excel objects, you will also find the ColorIndex property for these objects.

The ColorIndex property is a legacy from way back versions of Excel, but still supported.

The idea here is a fixed collection of 56 colors, each assigned its own numeric index. See the table below:

If these 56 colors satisfy your eye, you can simply assign any of these index numbers to the ColorIndex property of an object to set (or read) its color, like that:

Range("A1").Interior.ColorIndex = 8

Hey, don’t forget to share this Blog post with others! Thanks!