Excel Color Palette has an index of 56 colors which can be modified using VBA. Each color in the palette is associated with a unique value in the index that can be changed programatically. At times it is useful to know the relative positioning of the various colors within this index as well as how various versions of Excel treat colors. This is the first in the series of articles that will expore this further.
How to generate Excel Color Index using VBA
Here’s is a sample code snippet (with a few minor modifications) from mvps.org that helps one generate a color palette and place it an Excel worksheet :
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 | Sub colors56() '57 colors, 0 to 56 Dim i As Long Dim str0 As String, str As String Cells(1, 1) = "Interior" Cells(1, 2) = "Font" Cells(1, 3) = "HTML" Cells(1, 4) = "RED" Cells(1, 5) = "GREEN" Cells(1, 6) = "BLUE" Cells(1, 7) = "COLOR" For i = 0 To 56 Cells(i + 2, 1).Interior.ColorIndex = i Cells(i + 2, 2).Font.ColorIndex = i Cells(i + 2, 2).Value = "[Color " & i & "]" str0 = Right("000000" & Hex(Cells(i + 2, 1).Interior.Color), 6) 'Excel shows nibbles in reverse order so make it as RGB str = Right(str0, 2) & Mid(str0, 3, 2) & Left(str0, 2) 'generating 2 columns in the HTML table Cells(i + 2, 3) = "#" & str Cells(i + 2, 4).Formula = "=Hex2dec(""" & Right(str0, 2) & """)" Cells(i + 2, 5).Formula = "=Hex2dec(""" & Mid(str0, 3, 2) & """)" Cells(i + 2, 6).Formula = "=Hex2dec(""" & Left(str0, 2) & """)" Cells(i + 2, 7) = "[Color " & i & "]" Next i End Sub |
The output of the code will be something akin to what is shown below.
(Please note: Values -1 and 0 can be assigned to an object. However you cannot change those color values in the palette which would lead me to assume that Excel provides 58 (56 + 2) assignable color values while there being only 56 modifiable color values.)
Look’s downright ugly isn’t it. The interesting thing is that there seems to be no apparent logic to the allocation of color index values to various colors – it neither proceeds from light (#FFFFFF) to dark (#000000) or follow a sequential numbering pattern in the palette (left to right or up to down). One would think that the simplest way for anyone to create the palette would have been to number the colors in some logical fashion so that those colors could be modified using a program far more easily than by having to remember the position of each individual color in the palette.
You can download a sample worksheet with the Excel Color Index and Palette with VBA code here or click on the button below:
How to change Excel Color Palette using VBA
You can use the assign a new color to a the palette at a particular index position by simple specifying the index number and then using the .Colors function to assign a new RGB value to it. The R, G and B signify the red, green and blue hues that make up the color. (You can get the color index from the index chart shown above)
1 2 3 4 5 | Sub change_palette_color dim color_index as long color_index = 10 ActiveWorkbook.Colors(color_index) = RGB(128, 128, 128) End sub |
How to change Excel Color Palette manually in Excel 2007
Now Excel 2007 is a completely different ball of wax when it comes to color handling. The color palette theme can be change by accessing the “Colors” option under the “Page Layout” ribbon.
You can also define new color combinations (or select from a host of pre-existing ones) by using the options provided under this menu. The good part is that the color combinations are not just a mix of the existing 56 colors but new colors, which means that when you choose a new color theme, the RGB values of the new colors are different from the ones you had previously.
Accessing Color to object in Excel 2007 using VBA
The number of colors in the color index remains that same at 56. However additional properties such as .ThemeColor and .TintAndShade enable to work with nearly unlimited colors. This is a major improvement over Excel 2003 which used to “snap” custom colors to the ones which were already existing in the palette. Therefore, a piece of code would generate different results in Excel 2003 and 2007:
1 2 3 4 5 | Sub test_color() For i = 1 To 256 Cells(i, 1).Interior.Color = RGB(i, i, i) Next i End Sub |
As you can see, in Excel 2007 there is a smooth gradient from the darkest to the lightest hue while in Excel 2003, the colors change in bands.
As I said before, you can use the .ThemeColor and .TintAndShade properties to change color tint and theme:
1 2 3 4 5 6 | Sub test_color() For i = 1 To 256 Cells(i, 1).Interior.ThemeColor = xlThemeColorAccent1 Cells(i, 1).Interior.TintAndShade = (i * 2 - 256) / 256 Next i End Sub |
(Just bear in mind that you can enter a number from -1 (darkest) to 1 (lightest) for the TintAndShade property with 0 (zero) being neutral. Attempting to set this property to a value less than -1 or more than 1 results in a run-time error: “The specified value is out of range.” This property works for both theme colors and nontheme colors.)
very useful and clearly. tnx
January 30th, 2010 at 2:55 amThanks this is are really good article and the VBA code was really useful.
August 4th, 2010 at 9:35 amI have a spreadsheet which uses a group of 20 entries. Each of the 20 has only three color options: red, green or purple. I can’t use conditional formatting because the number entries is over 3.
I think that your approach will work, but I have no idea how to implement it. Any advice would be greatly appreciated. Thanks.
October 13th, 2011 at 1:15 pmNice work. On your Excel colour palette you’ve repeated 38 in the bottom row.
November 14th, 2011 at 9:36 amGreat Post, Do you know how I can assign a different color to every row in a table that has around 50 rows? Is there a vbscript I can use?
August 30th, 2012 at 7:45 amCheers
really helpful, i can do the cell count by color now! thanks~
October 10th, 2012 at 1:23 amThis is great. A really useful VBA application.
January 22nd, 2013 at 4:41 pm