VBA Color Index

Updated on January 1, 2024
Article byJeevan A Y
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

Excel VBA Color Index

Like in a worksheet, we change the color of a cell or a given cell range from the “Home” tab in VBA. In addition, we have a VBA function called “Color Index,” used to change the colors of the cells or the cell range provided. This function has unique identification for different types of colors.

In VBA, we have two ways of applying the color. The first uses the “Color” property, and the second uses the “ColorIndex” property.

We apply color to the cell as background, font, and border colors. So to access the color and color index property, we must first select the cell.

VBA Color Index

You are free to use this image on your website, templates, etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: VBA Color Index (wallstreetmojo.com)

How to use Color & Color Index Property?

You can download this VBA Color Index Template here – VBA Color Index Template

–>> If you want to learn Excel VBA professionally, then our VBA Basic Course (16+ hours) is the perfect solution. In our Basic Excel VBA course you learn the skill of automating tasks using Variables, Data Types, Ranges, and Cells in VBA. Master Control Structures, including Conditional Statements and Loops, and discover techniques for manipulating data through sorting, filtering, and formatting. By the end of the course, you will be able to apply your acquired skills to real projects, culminating in an Excel VBA project which will solidify your ability to create efficient, automated solutions.

#1 – Using Color Property

Assume you have the word “Hello” in cell A1. First, we will see how to apply the background color of this cell.

VBA Color Index Example 1

To change the background color in color properties, we need first to mention the range of the cell.

Code:

Sub Color()

 Range ("A1")

End Sub
VBA Color Index Example 1-1

After selecting the cell, we need to mention what we need to do. As we said, we need to change the interior color of the cell. So to change the background color of the cell, use the property “Interior.”

Code:

Sub Color()

  Range("A1").Interior

End Sub
VBA Color Index Example 1-2

Under the “Interior” property, we have several other methods and properties. Since we need to change the color of the cell, use the color property.

Code:

Sub Color()

 Range("A1").Interior.Color =

End Sub
Visual Basic interior color 1-3

Here, we can use eight constantly named colors. Below is the list of the same.

vbBlack, vbRed, vbGreen, vbBlue, vbYellow, vbMagenta, vbCyan, vbWhite

Select the color type as per your wish.

Code:

Sub Color()

 Range("A1").Interior.Color = vbBlue

End Sub
Visual Basic interior color 1-4

If we run this code using the F5 key or manually, it will change the background color of cell A1 to value.

VBA Color Index Example 1-5

Like this, you can use other constant named colors. Try using them to test the result.

Apart from these eight constant colors, we can also fill them with different colors by using the RGB function in VBARGB Function In VBARGB can be also termed as red green and blue, this function is used to get the numerical value of the color value, this function has three components as a named range and they are red, blue and green the other colors are considered as the components of these three different colors in VBA.read more.

Code:

Sub Color()

 Range("A1").Interior.Color = RGB(

End Sub
VBA Color Index Example 1-6

We must enter the mixture of red, green, and blue numbers. To use this, you should be perfect in entering the numbers. We have entered the mixture as RGB (250, 200, 150).

Code:

Sub Color()

Range("A1").Interior.Color = RGB(250, 200, 150)

End Sub
Visual Basic RGB 1-7

The change of this color is like this.

VBA Color index Example 1-8

The problem here is you do not know what number holds what color.

The below code is to change the font color.

Code:

Sub Color_Font()

Range("A1").Font.Color = RGB(100, 400, 100)

End Sub
VBA Color index Example 1-9

Run this code using the F5 key or manually to get the result.

VBA Color index Example 1-10

#2 – Using Color Index Property

Color Index is slightly different from the color property. For example, in the “COLOR” property, we use 8 constant colors given by VBA. Also, using the RGB function, we create our color.

The “COLOR INDEX” property is limited in VBA. It can hold values from 1 to 56. Each number from 1 to 56 holds different colors. Below are the list numbers and their respective colors.

Color Index

Below is the macro code to change the background color of cell A1 to pink.

Code:

Sub ColorIndex_Cell()

Range("A1").Interior.ColorIndex = 26

End Sub
VBA Color Index Example 2

Run this code manually or use the F5 key to see the result.

VBA Color Index Example 2-1

Below is the macro code to change the font color of cell A1 to yellow.

Code:

Sub ColorIndex_Font()

Range("A1").Font.ColorIndex = 27

End Sub
Visual Basic font color 2-4

Run this code using the F5 key, or you can run it manually and see the result.

VBA Color Index Example 2-2

One of the important things to remember here is we can enter numbers from 1 to 56. If anything more than 56, we will get the “Subscript Out Of Range” error.

VBA Color Index Example 2-3

Did You Observe?

As we said in the “Color Index,” the property can insert only 56 colors, but the fact is that it can only insert 46 unique colors, and there are 10 duplicate colors. Below is the list of duplicate color codes.

Duplicate color codes

Recommended Articles

This article is a guide to the VBA Color Index. Here, we learn the top ways to use the color and color index property in Excel VBA, along with some simple to advanced examples. Below are some useful Excel articles related to VBA: –