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.
Dive Deep into Macros! Make Excel do things you thought were impossible, discover techniques you won't find anywhere else, and create powerful automated reports. Bill Jelen and Tracy Syrstad help you instantly visualize information to make it actionable. You’ll find step-by-step instructions, real-world case studies, and 50 workbooks packed with examples and solutions. Check out Microsoft Excel 2019 VBA and Macros today!
Recording macros is a great approach to getting started with macros, but at some point you'll need to create one from ...
Discover MoreMacros are really helpful, but you may want to actually delete a macro from within another macro. This is not as easy as ...
Discover MoreMacros are very powerful, but you may not want them to always be available to a user. Here are some ways you can limit ...
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