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

In this article, we will learn to change cell color with VBA based on the value in Excel. We can use the conditional formatting feature to change cell colors. But with VBA, it can be done very quickly. Sometimes, when we show any result or performance in an excel report, we need to change cell color based on value. So, today, we will demonstrate 3 VBA examples to show how you can change cell color based on the value in excel easily.

Table of Contents hide

Download Practice Book

3 Examples to Change Cell Color Based on Value with VBA in Excel

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

2. Use Excel Command Button to Change Cell Color Based on Value of Another Cell

3. Dynamically Change Cell Color Based on Value with Excel VBA

Things to Remember

Conclusion

Download Practice Book

Download the practice book here.

VBA to Change Cell Color Based on Value.xlsm

3 Examples to Change Cell Color Based on Value with VBA in Excel

To explain these examples, we will use a dataset that contains information about the Sales Amount and the Comment on their performance. We will try to change the colors of the cells of Column D.

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

In the first example, we will change cell color based on the filled value. Here, the VBA code will change the cell color based on the value it contains. For example, if the cell contains Good, the cell color will be Green.

Let’s follow the steps to learn this example completely.

STEPS:

First of all, go to the Developer tab and select Visual Basic. This will open the Visual Basic window.

Secondly, select Insert and then, select Module in the Visual Basic window. The Module window will appear.

Thirdly, type the 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

Here, this VBA code will change the cell color. If the cell contains Good, Average, and Poor, the cell color will be green, yellow, and red respectively. You can also use different RGB color indexes.

Now, press Ctrl + S to save the code and then, close the Visual Basic window.
After that, select Macros from the Developer tab in the ribbon. It will open the Macro window.

Next, select the desired code and Run it from the Macro window.

Finally, you will see results like the picture below.

2. Use Excel Command Button to Change Cell Color Based on Value of Another Cell

In the second example, we will change the cell color based on the value of another cell. Here, we will use a command button. In this example, we will use the same dataset.

Let’s pay attention to the steps below to know more.

STEPS:

In the first place, go to the Developer tab.
Select Insert and a drop-down menu will occur.
Select Command Button from the ActiveX Controls.

Secondly, draw the command button like the picture below.

After that, double-click on the command button, and the Code window will appear inside the Visual Basic window.

Now, type the 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

Here, this code will change the colors of Cell D5 to D9 based on the values of Cell C5 to C9 respectively. If the cell value is greater than 2500 and less than 2650, then, the cell color will be red. If the cell value is greater than 2650 and less than 3000, then, it will be yellow. And, If the cell value is greater than 3000 and less than 3300, then, it will be green.

Then, press Ctrl + S to save the code and close the Visual Basic window.
Next, deselect Design Mode from the Developer tab.

In the end, click on the command button to change cell colors like the picture below.

Similar Readings:

3. Dynamically Change Cell Color Based on Value with Excel VBA

In the last example, we will change cell color dynamically based on cell value. For this example, we will use a dataset that contains Group 1 and Group 2 in Columns C & D respectively. Here, we will type a name in Group 1 and then, in Group 2. If both names do not match then, it will change the cell color of Group 2. If the names match, then, the cell color will not change.

Let’s observe the steps below to learn more.

STEPS:

Firstly, go to the Developer tab and select Visual Basic. It will open the Visual Basic window.

Then, 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.

Next, type the 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

Here, this code will compare the values of Columns B & C. If the value of Column C does not match with the respected value of Column B, then, the cell of Column C will be red. ColorIndex 3 represents red. You can color by changing the value of the color index.

Now, press Ctrl + S to save the code.
After that, close the Visual Basic window.
Here, type a name in Cell B2 and Cell C2 will automatically change the color to red. We have typed Mike in Cell B2.

Again, write Smith in Cell C2 and press Enter. As both names are different, Cell C2 remains red.

If you type the same names in both cells like Cell B3 and Cell C3, the color will not change.

Finally, you can type in all cells and see results like those below. Here, Cell B3, C3, and Cell B6, C6 did not change color because they contain the same value.

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.

Conclusion

We have demonstrated 2 easy examples of Change Cell Color Based on Cell Value with VBA in Excel. We have used different examples and also discussed the method to change cell color with the command button. I hope these methods will help you to perform your tasks easily. Furthermore, we have also added the practice book at the beginning of the article. You can download it to learn more. Last of all, if you have any suggestions or queries, feel free to ask in the comment section below.