VBA to Change Cell Color Based on Value in Excel (3 Easy Methods)

Suppose you have the following dataset:

Method 1 – Apply VBA to Change Cell Color in Excel Based on Filled Value

Steps:

• Go to the Developer tab and select Visual Basic. This will open the Visual Basic window.

• Select Insert and then select Module in the Visual Basic window. The Module window will appear.

• Type the following code in the Module window:

Sub Change_Cell_Color()
Dim xCell As Range
Dim CommentValue As String
Dim CommentRange As Range
Set CommentRange = Range("D5:D9")
For Each xCell In CommentRange
CommentValue = xCell.Value
Select Case CommentValue
    Case "Good"
    xCell.Interior.Color = RGB(0, 255, 0)
    Case "Average"
    xCell.Interior.Color = RGB(255, 255, 0)
    Case "Poor"
    xCell.Interior.Color = RGB(255, 0, 0)
End Select
Next xCell
End Sub

• Press Ctrl + S to save the code and close the Visual Basic window.
• Select Macros from the Developer ribbon and the Macro window will open.

• Select the applicable code and click Run.

Cells should now be properly colored.

Read More: Excel Formula Based on Cell Color

Method 2 – Use the Excel Command Button to Change Cell Color Based on the Value of Another Cell

Steps:

• Go to the Developer tab.
• Select Insert and a drop-down menu will open.
• Select Command Button from the ActiveX Controls.

• Create a command button similar to the example below.

• Double-click on the command button and the Code window will appear inside the Visual Basic window.

• Type the following code in the Code window:

Private Sub CommandButton1_Click()
Dim x As Long, xCell1 As Range, xCell2 As Range
For x = 5 To 9
Set xCell1 = Range("C" & x)
Set xCell2 = Range("D" & x)
If xCell1.Value >= 2500 And xCell1.Value < 2650 Then _
xCell2.Interior.Color = vbRed
If xCell1.Value >= 2650 And xCell1.Value < 3000 Then _
xCell2.Interior.Color = vbYellow
If xCell1.Value >= 3000 And xCell1.Value < 3300 Then _
xCell2.Interior.Color = vbGreen
Next x
End Sub

• Press Ctrl + S to save the code and close the Visual Basic window.
• Deselect Design Mode in the Developer ribbon.

• Click on the command button to change cell colors.

Method 3 – Dynamically Change Cell Color Based on Value with Excel VBA

Steps:

• Go to the Developer tab and select Visual Basic. It will open the Visual Basic window.

• Select the sheet where you want to apply the VBA code and right-click on it. A drop-down menu will appear.
• Select View Code from the drop-down menu.

• Type the following code in the Code window:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim xRange As Range
Dim R As Long
If Not Intersect(Range("B:C"), Target) Is Nothing Then
    Application.EnableEvents = False
    Application.ScreenUpdating = False
For Each xRange In Intersect(Range("B:C"), Target)
    R = xRange.Row
    If Range("B" & R) = Range("C" & R) Then
       Range("C" & R).Interior.ColorIndex = xlColorIndexNone
    Else
       Range("C" & R).Interior.ColorIndex = 3
    End If
Next xRange
Application.ScreenUpdating = True
Application.EnableEvents = True
End If
End Sub

• Press Ctrl + S to save the code and close the Visual Basic window.
• Type a value in the appropriate cell (B2) and the other cell (C2) will automatically change to red.

• Enter a value in the second cell (C2) and press Enter. Since the names are different, the cell remains red.

If both cells have the same value, the color will not change.

Read More: How to Change Cell Color Based on a Value in Excel

Things to Remember

After running the VBA codes, if you change any values, the result will not be automatically updated. You need to run the code again to see the updated result.

Download Practice Book

Download the practice book here.

Related Articles

• Uses of CELL Color A1 in Excel
• How to Color Code Cells in Excel
• Excel Formula to Change Cell Color Based on Text
• How to Fill Color in Cell Using Formula in Excel
• How to Fill Cell with Color Based on Percentage in Excel
• Excel Formula to Color Cell If It Has Specific Value
• How to Change Text Color with Formula in Excel

<< Go Back to Color Cell in Excel | Excel Cell Format | Learn Excel