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. We have a vba function called as Color Index which is used to change the colors of the cells or the cell range provided, this functions has unique identification for different type of colors.
In VBA we have two ways of applying the color, the first one is by using “color” property and the second one is using the “ColorIndex” property.
We apply color to the cell as a background color, we apply font color, and we apply border color. So to access color & color index property we need to first select the cell.
How to use Color & Color Index Property?
#1 – Using Color Property
Assume you have the word “Hello” in the cell A1. We will see how to apply the background color of this cell.
To change the background color, in colors properties we need to first mention the range of the cell.
Sub Color() Range ("A1") End Sub
After selecting the cell we need to mention what is the thing we need to do. As I said we need to change the interior color of the cell. So to change the background color of the cell use the property “Interior”.
Sub Color() Range("A1").Interior End Sub
Under Interior property, we have several other methods and properties. Since we need to change the color of the cell use color property.
Sub Color() Range("A1").Interior.Color = End Sub
Here we can use 8 constantly named colors. Below is the list of the same.
4.6 (247 ratings) 3 Courses | 12 Hands-on Projects | 43+ Hours | Full Lifetime Access | Certificate of Completion
Select the color type as per your wish.
Sub Color() Range("A1").Interior.Color = vbBlue End Sub
If I run this code using the F5 key or manually it will change the background color of the cell A1 to vbBlue.
Like this, you can use other constant named colors. Try using them to test the result.
Apart from these 8 constant colors, we can also fill with different colors as well by using RGB function in VBA.
Sub Color() Range("A1").Interior.Color = RGB( End Sub
We need to enter the mixture of Red, Green, and Blue in numbers. To use this you should be perfect in entering the numbers. I have entered the mixture as RGB (250, 200, 150).
Sub Color() Range("A1").Interior.Color = RGB(250, 200, 150) End Sub
Change of this color is like this.
The problem here is you don’t know exactly what number holds what color.
The below code is to change the font color.
Sub Color_Font() Range("A1").Font.Color = RGB(100, 400, 100) End Sub
Run this Code using the F5 key or manually to get the result.
#2 – Using Color Index Property
Color Index is slightly different from the color property. In COLOR property we use 8 constant colors given by VBA and also using RGB function we create our own color.
COLOR INDEX property is limited in VBA, it can hold the values from 1 to 56. Each number from 1 to 56 holds different colors. Below are the list numbers and their respective colors.
Below is the macro code to change the background color of the cell A1 to pink.
Sub ColorIndex_Cell() Range("A1").Interior.ColorIndex = 26 End Sub
Run this code manually or using the F5 key to see the result.
Below is the macro code to change the font color of the cell A1 to yellow.
Sub ColorIndex_Font() Range("A1").Font.ColorIndex = 27 End Sub
Run this code using the F5 key or you can run manually and see the result.
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.
Did You Observe?
As I told Color Index 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.
This has been a guide to VBA Color Index. Here we learn the top ways to use Color and Color Index property in Excel VBA along with some simple to advanced examples. Below are some useful excel articles related to VBA –