VBA ColorIndex: Add Some Color to Your Spreadsheets

When you’re working with Microsoft Excel on a daily basis, one of the most frustrating experiences is not being able to find the data you need, when you need it. Of course, there are various ways for searching and sorting the contents of a spreadsheet, so you will eventually find what you are looking for, but what if there was an easier way to do it?

Meet the Excel VBA ColorIndex property – a feature that will allow you to assign colors to various elements of a spreadsheet, and define certain actions to be performed, based on the colors of those elements. In case you’re not yet familiar with VBA for Excel, consider taking this 2-part online course on VBA for Excel to get a clearly-explained non-technical approach to VBA and macros in Excel, as using the VBA ColorIndex property effectively relies on having a good understanding of Excel VBA.

VBA ColorIndex in a Nutshell

The VBA ColorIndex is a property used to assign a certain color to some elements, such as a cell’s background or its text, from a palette of 56 pre-defined colors, via VBA programming. Unlike the Color property, which performs a fairly similar action, the ColorIndex property has the advantage of being easier to use, at the cost of being a bit less customizable. This is because the Color property allows the user to define an exact color using numeric, hexadecimal or RGB values, while ColorIndex assigns a color based on its corresponding number in the palette. ColorIndex still allows a fair amount of customization by allowing the user to manually define the 56 colors of the palette. Having an exact shade of a color might not be crucial if you only plan to use colors to be able to quickly differentiate one type of data from another, but if you work in a medium that requires the use of accurate colors, such as printing, you might consider using the Color property instead, to make sure you get exactly the color you need. You can learn more about the science of colors from this online course.

The Color Palette

In order to use the ColorIndex property, you will need to know each color’s corresponding value in the color palette. You can find out these values by opening the VBA editor (Alt + F11) and running the following code:

Sub DisplayPalette()
Dim N As Long
For N = 1 To 56
Cells(N, 1).Interior.ColorIndex = N
Next N
End Sub

This will create a macro called DisplayPalette. When you run that macro, you will notice that the cells 1 to 56 of the first column have changed color; the row number of each cell is the corresponding ColorIndex value for that specific color, so you would have to set the ColorIndex parameter to 1 for black, 3 for red, 5 for blue and so on. If the colors you obtain by running the code don’t match the ones described here, it means that your color palette may have been modified, but there’s no need to worry, as you only need to add the following line of code to bring it back to its default settings:

ActiveWorkbook.ResetColors

Applying Colors to Elements

Now that you learned how to display the color palette, it’s time to learn how to use it. Knowing how to apply colors to your spreadsheet with the help of VBA code will greatly increase your productivity; after all, color-coding various elements is one of the tricks Excel experts use to speed up their routines. In fact, it is one of the practices that are used on Wall Street, along with a few other tips and tricks – check out this online course on increasing Excel proficiency and you will be an Excel expert too.

Applying a color to an element is quite simple; here are the syntaxes for 3 of the most common elements – cell backgrounds, fonts and borders, used in a practical example:

Sub DisplayPalette()
Dim N As Long
For N = 1 To 56
Cells(N, 1).Interior.ColorIndex = N
ActiveWorkbook.ResetColors
Worksheets("Sheet1").Range("D5") = "Udemy Blog"
Worksheets("Sheet1").Range("C4:E6").Interior.ColorIndex = 15
Worksheets("Sheet1").Range("D5").Interior.ColorIndex = 10
Worksheets("Sheet1").Range("D5").Font.ColorIndex = 2
Worksheets(1).Range("C4:E6").Borders.ColorIndex = 1
Next N
End Sub

The result should look something like this:

As you can see, the code generated the color palette on column A, reset it to its default values, added a piece of text to a cell and applied background colors to cells (using the Interior property), to fonts (using the Font property) and to borders (using the Borders property) – everything by using simple values from the ColorIndex. 

 Customizing the Color Palette

The Color Palette used by the ColorIndex property can be customized to fit your needs, thus allowing you to define a certain color one time, and then just use its corresponding index number to easily apply it afterwards to your elements.

In order to customize a color in the palette, the following code is used:

ActiveWorkbook.Colors(3) = RGB(0, 0, 255)

You can use RGB combinations to define custom concentrations of red (the first element), green (the second element) and blue (the third element), on a scale from 0 to 255, in order to obtain a new color; the newly-obtained color will replace a color in the default ColorIndex, as per the specifications of the code. In this particular example, the 3^rd color of the ColorIndex default palette, red, will be replaced by the color defined by the RGB code – blue.

For example, applying the following code will replace the 15^th color of the ColorIndex, a light shade of grey, with a shade of purple.

ActiveWorkbook.Colors(15) = RGB(140, 0, 255)

With this code, of you will need to use purple within your spreadsheet again , instead of using the Color property to define the RGB value of the color every time you need it, you will simply set the parameter of the ColorIndex property to 15. 

To learn how to use VBA programming to manipulate data, depending on the colors assigned to it via the ColorIndex property, check out this ultimate Excel VBA online course.

VBA Color Palette vs. User Color Palette

If you have been wondering why should you complicate yourself with VBA programming to assign colors and not just do the task manually, with the “Fill Color” and “Font Color” options from the graphical interface of the Excel, the answer is simple: the result won’t be the same.

Microsoft Excel uses two different color palettes: one that is accessible via the graphical user interface, and one that can be accessed via VBA programming. Despite the fact that, by default, both palettes look the same and have the same color order, they are two completely different elements. An interesting fact is that the two color palettes are connected to each other, but in a one-way manner, meaning that making changes to some colors in the VBA palette will also apply the changes to the other palette, while changes to the other palette won’t affect the VBA palette. For this reason, it is best to use the VBA color palette.

Colors – Are They For You?

As you can see, knowing how to master colors, especially when doing so directly from VBA programming, can prove to be a huge performance boost in your day-to-day productivity, but if you want to go even more visual, consider taking an in-depth online course on Excel charts as well.

If you’re still not convinced of the utility of colors in a spreadsheet, or you think it is just not your thing, check out Richa’s blog post on sorting data in Excel – you will find plenty of other ways to do it.