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.
Download Practice Book
Download the practice book here.
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.
- 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.
- 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.
- How to Highlight Cells Based on Text in Excel [2 Methods]
- How to Fill Color in Excel Cell Using Formula (5 Easy Ways)
- Excel Cell Color: Add, Edit, Use & Remove
- How to Highlight a Row in Excel (5 Quick Methods)
- How to Highlight from Top to Bottom in Excel (5 Methods)
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.
- 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.
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.
- How to Compare Two Excel Sheets and Highlight Differences (7 Ways)
- Highlight Cells That Contain Text from a List in Excel (7 Easy Ways)
- How to Highlight Cell Using the If Statement in Excel (7 Ways)
- Highlight Every 5 Rows in Excel (4 Methods)
- How to Fill Cell with Color Based on Percentage in Excel (6 Methods)