How to Use ColorIndex in Excel VBA (4 Examples)

Excel VBA ColorIndex Codes

Check out the picture below for the ColorIndex of all the colors available in Excel VBA.

Color Codes to Use the ColorIndex Property in Excel VBA


Here’s a dataset with the Names, Starting Salaries, and Present Salaries of some employees of Jupyter Group.

Data Set to Use the ColorIndex Property in Excel VBA

We aim to see various uses of the ColorIndex property of VBA on this data set.


Example 1 – Set the Cell Background Color Using ColorIndex in Excel VBA

We’ll 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


Example 2 – Set the Cell Font Color Using ColorIndex in Excel VBA

We’ll 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.


Example 3 – Set the Cell Border Color Using ColorIndex in Excel VBA

We’ll 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.


Example 4 – Set the Cell Color to Another Cell’s Color Using ColorIndex

We’ll change the background color of cell B5 to green.

Now, we’ll change the background color of cell D5 to match 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 to match cell B5.

Output to Use the ColorIndex Property in Excel VBA

Read More: How to Get the Cell Color in Excel (2 Methods)


Download the Practice Workbook

Download this workbook to practice.

Get FREE Advanced Excel Exercises with Solutions!
Rifat Hassan
Rifat Hassan

Rifat Hassan, BSc, Electrical and Electronic Engineering, Bangladesh University of Engineering and Technology, has worked with the ExcelDemy project for almost 2 years. Within these 2 years, he has written over 250 articles. He has also conducted a few Boot Camp sessions on effective coding, especially Visual Basic for Applications (VBA). Currently, he is working as a Software Developer to develop and deploy additional add-ins to enhance the customers with a more sophisticated experience with Microsoft Office Suits,... Read Full Bio

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

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo