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.
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.
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.
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.]
⧠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
⧠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.
Similarly, you can change the font color or border color of any cell according to that of another cell using the ColorIndex property.
Read More: How to Get the Cell Color in Excel (2 Methods)
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.
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.