How to Change Cell Color Using VBA in Excel

Changing the cell background color can have a huge impact on the data you present in Excel.

Whether it’s highlighting some important cells or giving color to all the cells that contain a value lower or higher than a specific threshold, you can use the cell background color efficiently in many different situations.

VBA can make it easy and fast for you to quickly highlight a range of cells.

In this article, I will show you some powerful examples where you can use VBA to color cells in Excel.

But let’s start with the basics.

VBA Code to Change Cell Color of a Range

Below is the VBA macro code that would change the color of the range A1:A10 to yellow.

Sub ChangeCellColor()

' Change the range background color to yellow
Worksheets("Sheet1").Range("A1:A10").Interior.Color = RGB(255, 255, 0)

End Sub

The above code first refers to the range using Worksheets(“Sheet1”).Range(“A1:A10”) and then changes the value of the Interior.Color property to RGB(255, 255, 0).

You can make this code slightly easier to read by assigning the range name to a variable, as shown in the below code (it does the exact same thing)

Sub ChangeCellColor()

Dim myrange As Range

'Set the reference to the range to myrange variable
Set myrange = Worksheets("Sheet1").Range("A1:A10")

' Change the range background color to yellow
myrange.Interior.Color = RGB(255, 255, 0)

End Sub

Note: You can also use built-in VBA color constants such as vbYellow instead of RGB. So instead of myrange.Interior.Color = RGB(255, 255, 0), you can use myrange.Interior.Color = vbYellow

VBA Code to Change Cell Color Based on Cell Value

We can use VBA code to change the background color of cells in an Excel dataset based on the cell values.

The following dataset shows particular states’ annual sales and ratings.

We want to use VBA to change the background color of the cells in column C based on the rating value – green for “High,” yellow for “Medium,” and red for “Low.”

We use the steps below:

1. Press Alt + F11 to open the Visual Basic Editor.
2. Click “Insert” on the menu bar and choose the “Module” option to insert a standard VBA module.

3. Copy and paste the following code into the module:

Sub Change_Cell_Background_Color()
Dim rCell As Range
Dim RatingValue As String
Dim RatingRange As Range
Set RatingRange = Range("C2:C6")
    For Each rCell In RatingRange
    RatingValue = rCell.Value
        Select Case RatingValue
            Case "High"
            rCell.Interior.Color = RGB(0, 255, 0) 'Green
            Case "Medium"
            rCell.Interior.Color = RGB(255, 255, 0) 'Yellow
            Case "Low"
            rCell.Interior.Color = RGB(255, 0, 0) 'Red
        End Select
    Next rCell
End Sub

Note: You can modify the target range to suit your specific needs. We recommend that you add comments to the code describing the intended colors to make the code more readable.

4. Press Ctrl + S or click the “Save” button on the toolbar and click “No” on the message box.

5. Save the workbook as an Excel Macro-Enabled Workbook.

6. Press Alt + F11 to switch to the active worksheet containing the dataset.
7. Press Alt + F8 to open the “Macro” dialog box, select the “Change_Cell_Background_Color” macro on the “Macro name” list box, and click “Run.”

The VBA code executes and changes the background color of the cells in column C, as shown below:

Explanation of the Code

This VBA code changes the background color of cells in the range “C2:C6” based on their content. Cells containing “High” are colored green, “Medium” are colored yellow, and “Low” are colored red.

Note: In this example, we have used the RGB function to create colors by combining red, green, and blue components.

Also read: Change Cell Color Based on Value of Another Cell in Excel

VBA Code to Change Cell Color Based on the Value of Another Cell

We can use VBA code to change the background color of cells in an Excel dataset based on the values of other cells.

The following dataset shows particular states’ annual sales and ratings.

We want to use VBA to change the background color of the cells in columns A and B based on the values of corresponding cells in column C.

We use the following steps:

1. Press Alt + F11 to open the Visual Basic Editor (VBE).
2. Click “Insert” on the menu bar and choose “Module” to insert a standard VBA module.

3. Copy and paste the following code into the module:

Sub Change_Cell_Color()
    Dim i As Long, r1 As Range, r2 As Range
    For i = 2 To 6
        Set r1 = Range("C" & i)
        Set r2 = Range("A" & i & ":B" & i)
        If r1.Value <= 200000 Then r2.Interior.Color = vbRed
        If r1.Value > 200000 And r1.Value < 400000 Then r2.Interior.Color = vbYellow
        If r1.Value >= 400000 Then r2.Interior.Color = vbGreen
    Next i
End Sub

Note: You can customize the code for your needs by changing the target ranges and the value conditions.

4. Save the Workbook as an Excel Macro-Enabled Workbook, as shown in Example #1.
5. Press Alt + F11 to switch to the current workbook containing the dataset.
6. Press Alt + F8 to activate the “Macro” dialog box, select the “Change_Cell_Color” macro on the “Macro name” list box, and click “Run.”

The code executes and changes the background color of the cells in columns A and B, as shown below:

Explanation of the Code

The VBA code applies different background colors to cells in columns A and B based on the value in the corresponding cells in column C.

Red is used for values less than or equal to 200,000, yellow for values between 200,000 and 400,000, and green for values greater than or equal to 400,000.

Note: For this instance, we utilized color constants such as vbRed to modify the background color of cells instead of applying the RGB function, as demonstrated in Example #1.

Also read: How to Filter by Color in Excel?

VBA Code to Change Cell Color of Cells Containing Weekend Dates

You can use Excel VBA code to change the background color of cells containing weekend dates.

Let’s consider the below dataset containing dates from August 2023.

We want to use VBA to highlight weekend dates in light gray.

We use the following steps:

1. Press Alt + F11 to open the Visual Basic Editor.
2. Click “Insert” on the menu bar and choose “Module” to insert a standard VBA module.

3. Copy and paste the following code into the module:

Sub HighlightWeekends()
    Dim cell As Range
    Dim dateRange As Range
    Dim weekendColor As Long
    weekendColor = RGB(220, 220, 220) ' Light Gray
    Set dateRange = Range("A2:A32")

    For Each cell In dateRange
        If Weekday(cell.Value, vbSunday) = 1 Or Weekday(cell.Value, vbSunday) = 7 Then
            cell.Interior.Color = weekendColor
        End If
    Next cell
End Sub

4. Save the workbook as an Excel Macro-Enabled Workbook, as explained in Example #1.
5. Press Alt + F11 to switch to the current workbook containing the dataset.
6. Press Alt + F8 to activate the “Macro” dialog box, select the “HighlightWeekends” macro on the “Macro name” list box, and click “Run.”

The code runs and changes the background color of the cells containing weekend dates to light gray, as shown below:

Explanation of the Code

The VBA code goes through the dates in cell range A2:A32 and highlights cells with weekend dates (Saturdays and Sundays) using a light gray color.

Also read: How To Highlight Weekends In Excel?

Using the RGB Function and Color Constants in VBA to Change Cell Colors

In the preceding examples, we have demonstrated that you can change cell colors using VBA code with either the RGB function or color constants like vbRed, vbGreen, etc. Although it’s a matter of your personal preference, it’s essential to understand the distinction.  

The RGB function allows you to create various custom colors by specifying the exact values for red, green, and blue components. This provision gives you more flexibility to match specific color schemes.

However, if you only need standard colors and want to avoid specifying RGB values each time, color constants provide a more straightforward approach.

Also read: VBA to Add Border to Cells In Excel

Advantages of Using VBA to Change Cell Color

Whereas we can use the options on the Excel user interface to apply background colors to cells, using VBA offers several advantages, such as the following:

• VBA code allows you to apply color changes to multiple cells simultaneously, saving time and effort.
• With VBA, you can easily change the color of cells based on complex conditions, which might be challenging to accomplish using options on the Excel user interface.
• With VBA code, you can ensure consistent color changes across various worksheets, workbooks, or even cells in the same worksheet, guaranteeing that your preferred color scheme is uniformly applied.

This tutorial showed three examples of VBA code to change cell colors in Excel. We hope you found the tutorial helpful.

Other articles you may also like:

• What is VBA in Excel?
• Count Rows using VBA in Excel
• Count Columns Using VBA in Excel
• VBA to Remove Duplicates in Excel
• Create New Workbook Using VBA in Excel
• How to Change Theme Colors in Excel?