Official Color Names in VBA

by Allen Wyatt
(last updated November 8, 2014)

Excel uses a color palette consisting of 56 colors. You can see these colors if you display the Patterns tab of the Format Cells dialog box. When creating macros in VBA, you may want to refer to these colors, by name, using constants.

Unfortunately, Excel's VBA doesn't have constants defined for each of the 56 colors in the palette. The only colors defined, by name, are members of the ColorConstants class, and there are eight members of the class: vbBlack, vbWhite, vbRed, vbGreen, vbBlue, vbYellow, vbMagenta, and vbCyan.

In VBA you can use the ColorIndex property to define which color you want to use from Excel's palette. The problem is that ColorIndex is not a color; it is an index into the palette. Thus, a ColorIndex of 1 is the first color in the palette, 2 is the second, and so on. You can see this in action by looking at the sample code at this URL:

http://www.ozgrid.com/VBA/ReturnCellColor.htm

This code examines the ColorIndex property for a cell and returns a color name. The name returned, however, is not a constant for the color; it is only a description of what color the palette at that index appears to be.

If you want to set the color of a cell, you actually should use the Color property. This property allows you to use the eight VBA color constants mentioned earlier. It just so happens that if you use these Color property to set the interior color of a cell, you'll find that the eight named colors correspond to ColorIndex values of 1 through 8. The following macro illustrates this nicely:

Sub CheckColors()
    Dim arr8Colors As Variant
    Dim i As Integer

    arr8Colors = Array( _
      vbBlack, vbWhite, vbRed, vbGreen, _
      vbBlue, vbYellow, vbMagenta, vbCyan)
    For i = 0 To 7
        Selection.Offset(i, 0).Interior.Color = arr8Colors(i)
        Selection.Offset(i, 1).Value = Selection.Offset(i, 0).Interior.ColorIndex
    Next i
End Sub

This correspondence for the first eight values between Color and ColorIndex should only be taken as an artifact of history, dating back to the days when Excel only allowed you to use eight colors—the eight colors defined with VBA constants. If you want to specify some other color for a cell, you should use the RGB function to specify the Color property, as shown here:

Selection.Interior.Color = RGB(128, 64, 255)

The RGB function allows you to specify the red, green, and blue components of any color. Each component can range in value from 0 to 255.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2778) applies to Microsoft Excel 97, 2000, 2002, and 2003.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Typing Check Marks into Excel

Need to enter a check mark into a cell? There are a number of ways you can get the desired character, depending on the font ...

Discover More

Jumping to a Footnote

Jumping to a specific footnote can be very handy if your document has a lot of footnotes in it. Word provides the capability ...

Discover More

Selected Cells Not Shaded

Does the pale coloring scheme used to mark selected cells by Excel 2007 drive you nuts? You aren't alone. Here's an idea of ...

Discover More

Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!

MORE EXCELTIPS (MENU)

Telling which Worksheets are Selected

If your macro processes information on a number of worksheets, chances are good that you need your macro to figure out which ...

Discover More

Retrieving Drive Statistics

Need to gather some information about the drives on a system? It can be pretty easy to do using a macro, as shown in this ...

Discover More

Deleting Macros from within a Macro

Macros are really helpful, but you may want to actually delete a macro from within another macro. This is not as easy as it ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments for this tip:

There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)

This Site

Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.

Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.

Links and Sharing
Share