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


Excel Formula, Excel Chart, Excel Macro, Excel VBA, Pivot Table Excel, Excel Dashboard

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=""> <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.

  4. Andrew Howe wrote:

    Nice work. On your Excel colour palette you’ve repeated 38 in the bottom row.

  5. jp wrote:

    Great 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?
    Cheers

  6. xiaoroupi wrote:

    really helpful, i can do the cell count by color now! thanks~

  7. al_Evan_PA wrote:

    This is great. A really useful VBA application.

  8. Abhijit Mukherjee wrote:

    Dear Admin,
    I am in another problem to execute an formula (CONCATENATE) in MS Excel.
    So, I want your kind help in this regard as follows:

    1. I want customized text formatting i.e. Text_Colour, Bold, Italic, Underline, separately or jointly using CONCATENATE and without using CONCATENATE.

    2. I want to use the Indian Rupee sign replacing ` in the same formula.

    I came to know that for some well wishers that such problem may over come with the help of VBA programme in Excel.

    If possible, please send in details to over-come my problems and help me get my desired results in a cell their in the attached Excel file.

    I am waiting for your reply as well as your kind help in this regard.

    Abhijit Mukherjee
    West Bengal.
    abhijit8383mukherjee@gmail.com

  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 [...]

  4. Ruby : 操作Excel的API介绍 | 南龙的小站 wrote:

    [...] Excel Color Palette and Color Index change using VBA [...]

  5. Highlighting a row depending on which cell i am clicked on? wrote:

    [...] of the line. If you change the ColorIndex number you get a different color. ColorIndex list try Excel Color Palette and Color Index change using VBA | Excel & VBA – Databison '.EntireColumn.Interior.ColorIndex = 8 Private Sub Workbook_SheetSelectionChange(ByVal Sh As [...]

Subscribe

Keep up with the latest stories - Delivered right to your inbox
feedburner

Translate

English flagChinese (Simplified) flagPortuguese flagGerman flagFrench flagSpanish flagJapanese flagArabic flagDutch flagHindi flagPolish flagSwedish flagFilipino flagHebrew flagIndonesian flagUkrainian flagThai flagTurkish flag
treeemap software for excel