VBA Color Index

VBA Color Index

VBA Color Index is a function by which we can change the color of the cell or text which is located in the Home menu under the Font section. Same can be done through VBA Macros. We can change the color of the text or background color of any cell by VBA Macro.

Example #1

It is very easy to color any cell or text with the help of VBA Coding.

For this, we have considered a text “Color” in a cell as shown below.

Now to color the selected cell of any choice through VBA coding, press Alt + F11. It will take us to the VBA window. Now insert a new Module. For this go to the Insert menu and click on Module option as shown below.

After that, we will get a blank module, where we will write the code for Color Index. For this, frame the body for code with Subcategory. For better understanding name the Subcategory with the function of use.

Code:

Sub Color()

End Sub

First, select the range and choose the cell which needs to be colored. Here we have cell B2 with text “Color”.

Code:

Sub Color()

Range("B2")

End Sub

Now type and select Interior as shown below. This has many properties to select.

Code:

Sub Color()

Range("B2").Interior

End Sub

After that type and select Color from the list. This will enable different colors to work on.

Code:

Sub Color()

Range("B2").Interior.Color

End Sub

In VBA there is only one way to select any color. Type “vb” before the name of the color. We can add and mix only 8 main base colors. Which are Red, Black, Green, Yellow, Blue, Magenta, Cyan and White. Type vb at the start of these colors as shown below.

To complete the type any color as per above format. Let’s consider vbGreen.

Code:

Sub Color()

Range("B2").Interior.Color = vbGreen

End Sub

Now run the code using F5 key or manually. We will see the color of the selected cell in the range is changed to Green color as shown below.

Example #2

We can change the color of any selected cell in the combined mixture of Red-Green-Blue (or commonly known as RGB). We can decide the color shade and amount of shade we want to give. More value given to any color will make it of dark shade.

For this, we will consider the same cell named “Color”. Now go to VBA window and open a new module. And in the new module write the Subcategory of Color as shown below.

Code:

Sub Color()

End Sub

Select the range or cell with the command Range and select cell “B2”.

Code:

Sub Color()

Range("B2")

End Sub

Now as shown in example-1 select Interior.Color which will enable all the applicable colors to that selected cell and as explained RGB give any amount of color shade range. Here we have given Red – 200, Green – 100 and Blue – 150.

Code:

Sub Color()

    Range("B2").Interior.Color = RGB(200, 100, 150)

End Sub

Once done, run the code. As we can see in below screenshot, the background color of cell B2 is changed from white to purple shade. By this, we can create as many colors as required.

Example #3

We have seen the process of changing the background color of any cell. Now we will how to change the cell content or font color. For this also we will consider the same text as seen in example-1.

Now go to VBA and open a new module. Once done, start writing the subcategory as shown below.

Code:

Sub Color()

End Sub

First select the range and choose the cell whose text needs to be colored. Here we have cell B2 with text “Color”.

Code:

Sub Color()

Range("B2")

End Sub

Now as seen in example-2, instead of Interior.Color, select Font.Color function as shown below. Font command will select the font and text selected cell.

And now use RGB to create the required color and implement it to selected cell text. Here we have given color range Red – 153, Green – 50  and Blue – 204.

Code:

Sub Color()

    Range("B2").Font.Color = RGB(153,  50,  204)

End Sub

Once done, run the code using the F5 key or manually.

We will see, the color of the text in cell B2 will now be changed to Purple color shade.

Example #4

Microsoft Excel has defined every possible color and given the color code as well. In the above examples, we used Interior command to change the color of any cell. And with the help of RGB, we selected the color shades as well. Now we will select the specific color code and change the background color of the selected cell with the help of Color.Index. Defined color indexes are below.

These Color Indexes are taken and available from the Microsoft website. Now to go to the VBA window and open a new module. And in a new module write the Subcategory.

Code:

Sub Color()

End Sub

First, select the range and choose the cell whose font needs to be colored. Here we have cell B2 with text “Color”.

Code:

Sub Color()

Range("B2")

End Sub

After writing Font. This will enable the fonts to excel.

And just after continuing the coding, write and select Color.Index and select any of the color code as shown above. Here we are selecting color index 10 which represents green.

Code:

Sub Color()

    Range("B2").Font.ColorIndex = 10

End Sub

Now run the code using F5 key or manually. Then we will see the font color of selected cell B2 is now changed to Green as shown below.

We can do this for Background color as well.

Pros of VBA Color Index

• It is as easy changing the color from VBA as changing the same from excel functions.
• Any color can be selected.

Things to Remember

• It is always recommended to use VBA Color Index when statistical data are used.
• Always save the file in Macro-Enabled Excel so that created code and macro can be used multiple times.
• Using color index beyond the list of 56 Colors is not possible.
• Color Index can be used for both Font and Background color change.

Recommended Articles

This has been a guide to Excel VBAColor Index. Here we discussed how to use Color Index in VBA along with some practical examples and downloadable excel template. You can also go through our other suggested articles–

1. VBA Arrays in Excel
2. Guide to VBA Number Format
3. Guide to VBA Find
4. How to use VBA Do While Loop?