VBA Color Index

What Is VBA Color Index In Excel?

VBA COLOR INDEX is the property used with numerical numbers to get the preassigned color to each number. We can assign a number index from 1 to 56, and each of these numbers represents different colors.

Also, we can apply colors by using Color and Color Index properties. However, there is a difference between these two, and we will discuss this in a while.

The following picture shows the color associated with each number.

Syntax Of Excel VBA Color Index

Following is the syntax of the VBA Color Index property:

Range(“Required Range”).Interior.ColorIndex = (Any number from 1 to 56)

The syntax may look strange, but this is the best way to understand the Color Index property.

Range(“Required Range”): To apply color formatting, we must decide which cells to format. Hence, providing the range property with cell addresses in double quotes is the first requirement.

For example, we are choosing the range A1 to A10 like the following.

Interior: Once the range is selected using the VBA RANGE property, we need to decide what we need to do with the selected range. So, since we are going to change the color of the cell, we need to choose the “Interior” property of the RANGE property.

We can select the Interior property of the range by entering dot (.) after the range property bracket.

Color Index: Once the Interior has been selected, we need to decide on the color index property because we are changing the color of the cell.

Any Number From 1 to 56: Once the color index property is selected, we need to set the index number between 1 to 56 by entering the equal sign. To assign a number, we need to know which index number holds which color.

How To Use Color & Color Index Property?

As we said earlier, we can set the color of the cell in two ways i.e., using the COLOR property and VBA COLOR INDEX property.

#1 – Using Color Property

The syntax of using the COLOR property is similar to the COLOR INDEX property we discussed earlier in this post. However, with the COLOR property, we will not use any index number rather, we will use VBA built-in colors.

We have 8 built-in VBA colors. They are:

1. vbRed
2. vbBlack
3. vbWhite
4. vbGreen
5. vbMagenta
6. vbCyan
7. vbBlue
8. vbYellow

We will get the following colors by using the above colors.

Sub Example1()

Range(“A1”).Interior.Color = vbGreen

End Sub

The following code will change the background color of cell A1 to blue.

Copy the above code and paste it into the Visual Basic Editor window.

Run this code by hitting the run button in the Visual Basic Editor window or use the shortcut key F5 to change the background color of cell A1 to Blue.

We can use any of the available 8 constant colors under the COLOR property.

RGB Function to Change the Color: Instead of using the 8 constant built-in colors, we can use the RGB function to change the color to any of the colors as per our need.

The following is the syntax of the RGB function in VBA.

RGB(Red As Integer, Green As Integer, Blue As Integer)

We can create our own color in a mixture of RED, GREEN, and BLUE.

Each color accepts integer numbers, so one should know the RED Index number, GREEN Index number, and BLUE Index number.

For example, if we want to apply only red color background to cell A1, then we need to give 250 to the RED argument of the RGB function, and the other colors should be 0.

Sub Color_Example1()

Range(“A1”).Interior.Color = RGB(250, 0, 0)

End Sub

To Apply Only Red Color

This will change the cell A1 color background to Red.

Similarly, we can use Green and Blue colors as well.

To Apply Only Green Color

Sub Color_Example1()

Range(“A1”).Interior.Color = RGB(0, 250, 0)   

End Sub

To Apply Only Blue Color

Sub Color_Example1()

Range(“A1”).Interior.Color = RGB(0, 0, 250)

End Sub

To test other colors, we can give any combination of numbers to see the color it applies. For example, in the following code, we have given 200 for Red, 300 for Green, and 400 for Blue arguments respectively.

Sub Color_Example1()

Range(“A1”).Interior.Color = RGB(200, 300, 400)

End Sub

When we run this code, we will get the following background color in cell A1.

#2 – Using Color Index Property

Instead of using the COLOR property, we can use the COLOR INDEX property as well. However, the VBA COLOR INDEX property is very limited colors.

We can give index numbers from 1 to 56 only.

For example, if we want to change the background color of cell A1 to Saffron, then we need to use the COLOR INDEX number as 46.

Sub Color_Example1()

Range(“A1”).Interior.ColorIndex = 46

End Sub

This will change the background color of cell A1 to Saffron like the following.

For this, we can use any numbers between 1 to 56 when we use the COLOR INDEX property in VBA.

Examples

Example #1 – Change The Font Color Using The COLOR Property

VBA COLOR Index property can be used to change the font color as well. For example, look at the following value in cell A1.

Assume we must change the cell A1 background color to Green and the font color to White. We need to use the following two properties before we use the COLOR property.

• Interior: To change the background color.
• Font: To change the font color.

The following code will change the background color to Green and the font color to White.

Sub Example1()

Range(“A1”).Interior.Color = vbGreen

Range(“A1”).Font.Color = vbWhite

End Sub

Run this code by pressing the F5 key to get the following result.

Example #2 – List All The Colors Of Color Index Numbers

While using the VBA COLOR INDEX numbers, we can give index numbers from 1 to 56. However, we cannot remember which number holds which color. To make our life easier, we have the following, which will list out the numbers and colors they hold.

Sub Example2()

Dim R As Integer
Dim C As Integer
Dim ColorNumber As Integer

ColorNumber = 1

For R = 1 To 8

    For C = 1 To 7

        Cells(R, C).Interior.ColorIndex = ColorNumber
        ColorNumber = ColorNumber + 1
    Next C

Next R

End Sub

When we run this code, we will get the following color matrix.

By looking at the above color chart, we can apply the color index number based on the color that we need.

Did You Find The Duplicates?

As we have learned so far, the Color Index property takes 1 to 56 integer numbers and gives 56 different colors. However, if we observe the above color matrix, we have many duplicate values.

There are 10 duplicate colors with COLOR INDEX property. The image shows the duplicate colors and the number associated with them. Overall, we have only 46 unique colors with Color Index property numbers.

Clear Colors In Excel VBA

Once the color is applied as a background color to cells, we may need to remove them at times. So, it is important to understand how to remove the color from cells.

Instead of using the color index number or VBA constant colors, we need to use the xlNone property to remove the colors.

Sub Remove_Color()

Range(“A1”).Interior.ColorIndex = xlNone

End Sub

For example, look at the following code.

We have the following color background in cell A1.

When we run the above code, it will set the color index property of cell A1 to none i.e., it will remove the background color of cell A1.

The above code will remove the color only from cell A1. However, we need to make the code more dynamic i.e., users will select the range of cells from which they need to remove the color and click on the button to remove the color from the selected range of cells.

Make Range Dynamic

Look at the following code.

Sub Remove_Color()

Dim Rng As Range

Set Rng = Selection

Rng.Interior.Color = xlNone

End Sub

First, copy the code and then, paste it into the Visual Basic Editor window.

Next, insert a rectangular shape button in the worksheet.

Insert this shape into the worksheet.

Enter the text as Click here to remove background color.

Right-click on the shape and choose the Assign Macro option.

Choose the macro name Remove_Color.

Next, click on OK, and Remove_Color macro will be assigned to this shape.

Now, choose the range of cells from which we need to remove the background color. For example, we have chosen the range of cells from A1:A8.

After selecting the targeted range, click on the macro assigned button.

There we go, as soon as we click the macro assigned button, the selected range background color has been removed.

In this way, by selecting and running the macro through this button, we can remove the background color in VBA.

Important Things To Note

• Color and Color Index are properties in VBA.
• To apply VBA color index and color properties, we need to first specify the range of cells or the targeted range of cells.
• For the VBA color index property, we can give index numbers only from 1 to 56. If the number is not in this range, then we will get the subscript out-of-range error.
• From 1 to 56, there are 10 duplicate colors that appear when we use the color index property.
• RGB function takes 3 arguments, and all those arguments are mandatory. We can only give integer numbers as the input.

Frequently Asked Questions

Download Template

This article must be helpful to understand the VBA Color Index, with its formula and examples. You can download the template here to use it instantly.

Recommended Articles

This has been a guide to VBA Color Index. Here we explain how to apply colors by using Color and Color Index properties with downloadable excel template. You may learn more from the following articles –

• VBA Option Explicit
• VBA Format Number
• VBA Macros