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.
Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!
Copying worksheets (one or many) is easy to do manually. What is not well known is that it is even easy to make the ...
Discover MoreRecording macros is a great approach to getting started with macros, but at some point you'll need to create one from ...
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."
2016-06-19 13:29:27
Emerogork
Is it possible to create my own VB Color Constants?
I have specific need for VBMyDarkBlue and VBMyLightBlue where RGB is not working. Is this possible?
RGB(146, 180, 240)
RGB(40, 90, 180)
2015-04-09 09:55:36
Thank you Scott Renz! I use the number scheme all the time, but it isn't very readable. With both the rgb name and descriptive name, my code will be more maintainable. Extremely useful!
2015-01-27 06:01:39
Ümran
THANK YOU VERY VERY MUCH!!!!
2014-11-17 06:51:44
Stefan Masic
Thanks for RGB tip function. I didn't know that this function exist. Super.
With best regards, Stefan Masic
2014-11-10 13:41:01
Kelly Runyon
Thank you Scott Renz for the color list!
2014-11-10 10:19:09
Scott Renz
Name Value Description
rgbAliceBlue 16775408 Alice Blue
rgbAntiqueWhite 14150650 Antique White
rgbAqua 16776960 Aqua
rgbAquamarine 13959039 Aquamarine
rgbAzure 16777200 Azure
rgbBeige 14480885 Beige
rgbBisque 12903679 Bisque
rgbBlack 0 Black
rgbBlanchedAlmond 13495295 Blanched Almond
rgbBlue 16711680 Blue
rgbBlueViolet 14822282 Blue Violet
rgbBrown 2763429 Brown
rgbBurlyWood 8894686 Burly Wood
rgbCadetBlue 10526303 Cadet Blue
rgbChartreuse 65407 Chartreuse
rgbCoral 5275647 Coral
rgbCornflowerBlue 15570276 Cornflower Blue
rgbCornsilk 14481663 Cornsilk
rgbCrimson 3937500 Crimson
rgbDarkBlue 9109504 Dark Blue
rgbDarkCyan 9145088 Dark Cyan
rgbDarkGoldenrod 755384 Dark Goldenrod
rgbDarkGray 11119017 Dark Gray
rgbDarkGreen 25600 Dark Green
rgbDarkGrey 11119017 Dark Grey
rgbDarkKhaki 7059389 Dark Khaki
rgbDarkMagenta 9109643 Dark Magenta
rgbDarkOliveGreen 3107669 Dark Olive Green
rgbDarkOrange 36095 Dark Orange
rgbDarkOrchid 13382297 Dark Orchid
rgbDarkRed 139 Dark Red
rgbDarkSalmon 8034025 Dark Salmon
rgbDarkSeaGreen 9419919 Dark Sea Green
rgbDarkSlateBlue 9125192 Dark Slate Blue
rgbDarkSlateGray 5197615 Dark Slate Gray
rgbDarkSlateGrey 5197615 Dark Slate Grey
rgbDarkTurquoise 13749760 Dark Turquoise
rgbDarkViolet 13828244 Dark Violet
rgbDeepPink 9639167 Deep Pink
rgbDeepSkyBlue 16760576 Deep Sky Blue
rgbDimGray 6908265 Dim Gray
rgbDimGrey 6908265 Dim Grey
rgbDodgerBlue 16748574 Dodger Blue
rgbFireBrick 2237106 Fire Brick
rgbFloralWhite 15792895 Floral White
rgbForestGreen 2263842 Forest Green
rgbFuchsia 16711935 Fuchsia
rgbGainsboro 14474460 Gainsboro
rgbGhostWhite 16775416 Ghost White
rgbGold 55295 Gold
rgbGoldenrod 2139610 Goldenrod
rgbGray 8421504 Gray
rgbGreen 32768 Green
rgbGreenYellow 3145645 Green Yellow
rgbGrey 8421504 Grey
rgbHoneydew 15794160 Honeydew
rgbHotPink 11823615 Hot Pink
rgbIndianRed 6053069 Indian Red
rgbIndigo 8519755 Indigo
rgbIvory 15794175 Ivory
rgbKhaki 9234160 Khaki
rgbLavender 16443110 Lavender
rgbLavenderBlush 16118015 Lavender Blush
rgbLawnGreen 64636 Lawn Green
rgbLemonChiffon 13499135 Lemon Chiffon
rgbLightBlue 15128749 Light Blue
rgbLightCoral 8421616 Light Coral
rgbLightCyan 9145088 Light Cyan
rgbLightGoldenrodYellow 13826810 LightGoldenrodYellow
rgbLightGray 13882323 Light Gray
rgbLightGreen 9498256 Light Green
rgbLightGrey 13882323 Light Grey
rgbLightPink 12695295 Light Pink
rgbLightSalmon 8036607 Light Salmon
rgbLightSeaGreen 11186720 Light Sea Green
rgbLightSkyBlue 16436871 Light Sky Blue
rgbLightSlateGray 10061943 Light Slate Gray
rgbLightSteelBlue 14599344 Light Steel Blue
rgbLightYellow 14745599 Light Yellow
rgbLime 65280 Lime
rgbLimeGreen 3329330 Lime Green
rgbLinen 15134970 Linen
rgbMaroon 128 Maroon
rgbMediumAquamarine 11206502 Medium Aquamarine
rgbMediumBlue 13434880 Medium Blue
rgbMediumOrchid 13850042 Medium Orchid
rgbMediumPurple 14381203 Medium Purple
rgbMediumSeaGreen 7451452 Medium Sea Green
rgbMediumSlateBlue 15624315 Medium Slate Blue
rgbMediumSpringGreen 10156544 Medium Spring Green
rgbMediumTurquoise 13422920 Medium Turquoise
rgbMediumVioletRed 8721863 Medium Violet Red
rgbMidnightBlue 7346457 Midnight Blue
rgbMintCream 16449525 Mint Cream
rgbMistyRose 14804223 Misty Rose
rgbMoccasin 11920639 Moccasin
rgbNavajoWhite 11394815 Navajo White
rgbNavy 8388608 Navy
rgbNavyBlue 8388608 Navy Blue
rgbOldLace 15136253 Old Lace
rgbOlive 32896 Olive
rgbOliveDrab 2330219 Olive Drab
rgbOrange 42495 Orange
rgbOrangeRed 17919 Orange Red
rgbOrchid 14053594 Orchid
rgbPaleGoldenrod 7071982 Pale Goldenrod
rgbPaleGreen 10025880 Pale Green
rgbPaleTurquoise 15658671 Pale Turquoise
rgbPaleVioletRed 9662683 Pale Violet Red
rgbPapayaWhip 14020607 Papaya Whip
rgbPeachPuff 12180223 Peach Puff
rgbPeru 4163021 Peru
rgbPink 13353215 Pink
rgbPlum 14524637 Plum
rgbPowderBlue 15130800 Powder Blue
rgbPurple 8388736 Purple
rgbRed 255 Red
rgbRosyBrown 9408444 Rosy Brown
rgbRoyalBlue 14772545 Royal Blue
rgbSalmon 7504122 Salmon
rgbSandyBrown 6333684 Sandy Brown
rgbSeaGreen 5737262 Sea Green
rgbSeashell 15660543 Seashell
rgbSienna 2970272 Sienna
rgbSilver 12632256 Silver
rgbSkyBlue 15453831 Sky Blue
rgbSlateBlue 13458026 Slate Blue
rgbSlateGray 9470064 Slate Gray
rgbSnow 16448255 Snow
rgbSpringGreen 8388352 Spring Green
rgbSteelBlue 11829830 Steel Blue
rgbTan 9221330 Tan
rgbTeal 8421376 Teal
rgbThistle 14204888 Thistle
rgbTomato 4678655 Tomato
rgbTurquoise 13688896 Turquoise
rgbViolet 15631086 Violet
rgbWheat 11788021 Wheat
rgbWhite 16777215 White
rgbWhiteSmoke 16119285 White Smoke
rgbYellow 65535 Yellow
rgbYellowGreen 3329434 Yellow Green
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 © 2018 Sharon Parq Associates, Inc.
Comments