Excel VBA, Cell Fill Color

In this article I will explain how you can set and get the fill color of cells using VBA. There are many times you may need to set the background colors of certain cells based upon a condition. Using conditional formatting is not always feasible as the formula may get very complex.

There are multiple ways to specify the color that you wish to use. Let us look at some examples

For more information about using colors in VBA for excel please see VBA Excel Colors.

Jump To:


Set:

The code below changes the fill color of cell “A1” to red:

'Using XlRgbColor Enumeration
Range("B2").Interior.Color = rgbDarkGreen
'Using Color Constants
Range("B3").Interior.Color = vbGreen
'Specifying the RGB values
Range("B4").Interior.Color = RGB(255, 0, 0)
'Using Color Code
Range("B5").Interior.Color = 15773696
'Using Color Index
Range("B6").Interior.ColorIndex = 6
'You can also specify no color or automatic color using
Range("B7").Interior.ColorIndex = xlNone
Range("B8").Interior.ColorIndex = xlColorIndexAutomatic

This is how the Excel will look before and after running the code. Before:

before vba fill code

After:

vba fill code after

For more information about color constants please see Excel VBA ColorConstants.

For a complete example on setting the background color of cells based on color codes please see Excel VBA Color Code.


Get:

The line below gets the color code of the color used to fill cell A1 and prints it in cell B1:

Cells(1, 2) = Range("A1").Interior.Color

The following line of code does the same:

Cells(1, 2) = Range(Cells(1, 1), Cells(1, 1)).Interior.Color

Result:

Excel VBA, Color Code
For an example on getting the fill color used in a cell or range please see Excel VBA, Get Color Code.

Example:

Let us look at a more practical example of changing the background color of cells. Say, in your Excel program, you have an option to change the color theme of a sheet (or part of a sheet). So, you have 2 color themes, black and blue and a button for each. This is how it looks before:

vba color change example

You will use the code below to – check the background color of each cell in the table. If it is black, change it to blue and vice-versa.

Private Sub SetBlack_Click()
Dim forecastTable As Range
Set forecastTable = Range("B7:K17")
For Each cell In forecastable
'Check the color of the cell	
If cell.Interior.color = 9851952 Then
'If it is blue, change it to black
cell.Interior.color = 0
End If
Next cell
End Sub
Private Sub SetBlue_Click()
Dim forecastTable As Range
Set forecastTable = Range("B7:K17")
For Each cell In forecastTable
'Check the color of the cell	
If cell.Interior.color = 0 Then
'If it is black, change it to blue
cell.Interior.color = 9851952
End If
Next cell
End Sub

This is how the Excel will look after clicking on Blue Theme:

vba color change fill example blue

Instead of checking the color of each cell, you can also directly specify the range for which you want to change the color. This example was used to demonstrate how to change the color of all cells from one value to another

See also:

If you need assistance with your code, or you are looking for a VBA programmer to hire feel free to contact me. Also please visit my website www.software-solutions-online.com

Leave a Reply

Your email address will not be published. Required fields are marked *