How to Use ColorIndex in Excel VBA (4 Examples)

In this article, I’ll show you how you can use the ColorIndex property in Excel VBA. You’ll learn to set the background, font, and border color of one or more cells using the ColorIndex property of VBA, as well as to set the color of one cell according to that of another.


Excel VBA ColorIndex Codes

Before going to the main discussion, look at the picture below to know the ColorIndex of all the colors available in Excel VBA.

Color Codes to Use the ColorIndex Property in Excel VBA


Download Practice Workbook

Download this practice workbook to exercise while reading this article.


4 Examples to Use the ColorIndex Property in Excel VBA

Here we’ve got a data set with the Names, Starting Salaries, and Present Salaries of some employees of a company called Jupyter Group.

Data Set to Use the ColorIndex Property in Excel VBA

Our objective is to see various uses of the ColorIndex property of VBA on this data set.


1. Set Cell Background Color Using ColorIndex in Excel VBA

You can set the cell background color to anything you wish using the ColorIndex property of VBA.

Let’s change the background color of the range B4:B13 to green.

VBA Code:

The line of code will be:

Range("B4:B13").Interior.ColorIndex = 10

[10 is the ColorIndex of the color green. See the color chart.]

Output:

Run this code, and you’ll find the background color of the range B4:B13 turned green.

Coloring Cell Background with ColorIndex of Excel VBA


2. Set Cell Font Color Using ColorIndex in Excel VBA

You can also set the font color of the text of any cell using the ColorIndex property of Excel VBA.

Let’s change the font color of the range B4:B13 to red.

VBA Code:

The line of code will be:

Range("B4:B13").Font.ColorIndex = 3

[3 is the ColorIndex of Red.]

VBA Code to Use the ColorIndex Property in Excel VBA

Output:

Run this code, and you’ll find the font color of the range B4:B13 turned red.


3. Set Cell Border Color Using ColorIndex in Excel VBA

Now we’ll set the color of the cell border using the ColorIndex property of VBA.

Let’s change the color of the border of the range B4:B13 to red.

VBA Code:

The line of code will be:

Range("B4:B13").Borders.ColorIndex = 3

VBA Code to Use the ColorIndex Property in Excel VBA

Output:

Run this code. It’ll change the color of the borders of the range B4:B13 to red.


4. Set Cell Color to Another Cell’s Color Using ColorIndex

Finally, I’ll show you can change the color of one cell according to the color of another cell.

Let’s change the background color of cell B5 to green.

Now, we’ll change the background color of cell D5 according to that of cell B5.

VBA Code:

The line of code will be:

Range("D5").Interior.ColorIndex = Range("B5").Interior.ColorIndex

Output:

Run this code. It’ll change the background color of cell D5 according to that of cell B5.

Output to Use the ColorIndex Property in Excel VBA

Similarly, you can change the font color or border color of any cell according to that of another cell using the ColorIndex property.


More Learning

In this article, we’ve changed the cell color of cells using the ColorIndex property of VBA.

Besides the ColorIndex property, there is another property called Color in VBA, which deals with colors.

Click here to know it in detail.


Conclusion

Using these methods, you can use the ColorIndex property of Excel to set the background, font, and border color of one or more cells with VBA in Excel. Do you have any questions? Feel free to ask us.

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from the Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

2 Comments
  1. I tried your colorindex function because i need to total cells up by their background color. It doesn’t work if you are getting the color of a cell that is within a table. Any suggestions.

    • Hi Bolton, thanks for reaching out. I think you need to use different code for your solution. The following code will create a user defined function to sum up the data based on the background color of cells.

      Function SumBasedOnColor(mn_cell_color As Range, mn_range As Range)
      Dim mn_sum As Long
      Dim mn_colorIndex As Integer
      mn_colorIndex = mn_cell_color.Interior.ColorIndex
      For Each mn_CI In mn_range
      If mn_CI.Interior.ColorIndex = mn_colorIndex Then
      mn_sum = WorksheetFunction.Sum(mn_CI, mn_sum)
      End If
      Next mn_CI
      SumBasedOnColor = mn_sum
      End Function

      After that, use the function like the following image.

      There’s something important that you have to keep in mind. We referenced the cell F4 here and this cell background color was filled with yellow. The cell you reference should have the background color matched with the cells containing data.

Leave a reply

ExcelDemy
Logo