Excel VBA ColorIndex Codes
Check out the picture below for the ColorIndex of all the colors available in Excel VBA.
Here’s a dataset with the Names, Starting Salaries, and Present Salaries of some employees of Jupyter Group.
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.
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.]
⧭ 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
⧭ 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.
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!
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.
Great post! The examples you provided are really helpful in understanding how to use ColorIndex in Excel VBA. I particularly found the section on changing cell colors based on conditions very practical. Thanks for sharing such useful insights!
Hello,
You are most welcome. Thanks for your feedback and appreciation. Glad to hear that our article helped you to understand VBA colorindex.
Regards
ExcelDemy