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
• Get

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:

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:

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:

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:

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:

• Excel VBA, Get Color Code
• Excel VBA Color Code
• VBA Excel Colors
• Excel VBA, Formatting Cells and Ranges using the Macro Recorder
• Excel VBA, Fill Pattern

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