Categorized | excel tips

Excel Color Palette and Color Index change using VBA




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.

excel-color-index-excel-2003

(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.

excel-color-index

You can download a sample worksheet with the Excel Color Index and Palette with VBA code here or click on the button below:

download Excel Color Index and Palette with VBA code

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.

excel-2007-color-index-and-palette

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.

define-new-custom-colors-in-excel-2007

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

excel-2003-and-2007-vba-color-code

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.)



What Do You Think ?


XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>


Comments and Trackbacks

  1. Andrija wrote:

    very useful and clearly. tnx

  2. James wrote:

    Thanks this is are really good article and the VBA code was really useful.

  3. Rick Bajackson wrote:

    I 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.

  1. Conditional Formatting + Custom Format = More than Three Colors | da TaB is On wrote:

    […] the following manner – – When the cell value is greater than -15, use the color whose value in the color index is 36 – When the cell value is greater than -40, use the color whose value in the color index is 40 […]

  2. All about Comment shapes ยป Code For Excel And Outlook Blog wrote:

    […] best part is the Intellisense on the shape type and gradient style. For the color index, see Excel Color Palette. One thing I noticed was Comment.Shape.Fill.ForeColor.SchemeColor doesn’t follow the same pattern […]

  3. Sparklines in Excel - New Features in Excel 2010 Series | Excel & VBA - da Tab Is On wrote:

    […] can be given custom colors of their own. Just select the color palette from the ‘Design’ tab and color them whichever way you […]



Translate

Chinese (Simplified)DutchEnglishFilipinoFrenchGermanHebrewHindiItalianJapanesePortugueseRussianSpanish

treeemap software for excel