Written by Allen Wyatt (last updated September 17, 2022)
This tip applies to Excel 97, 2000, 2002, and 2003
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.
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2778) applies to Microsoft Excel 97, 2000, 2002, and 2003.
Best-Selling VBA Tutorial for Beginners Take your Excel knowledge to the next level. With a little background in VBA programming, you can go well beyond basic spreadsheets and functions. Use macros to reduce errors, save time, and integrate with other Microsoft applications. Fully updated for the latest version of Office 365. Check out Microsoft 365 Excel VBA Programming For Dummies today!
Sometimes a macro command line can get very, very long. This can make it hard to understand when you look at it a month ...
Discover MoreExcel allows you to use worksheet functions from within macros. This is helpful, especially when you are trying to ...
Discover MoreRecording macros is a great approach to getting started with macros, but at some point you'll need to create one from ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)
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.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2025 Sharon Parq Associates, Inc.
Comments