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 :

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)

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:

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:

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