Excel VBA to Highlight Cell Based on Value (5 Examples)

Usually, we highlight cells in Excel based on different criteria. Conditional Formatting is one of the widely used features to highlight cells. We can apply other methods to highlight cells. In this article, we will highlight a cell based on the value using Excel VBA.

VBA Code to Highlight Cell Based on Value (A Quick View)

Sub multiple_conditional_formatting()
Dim range_1 As Range
Dim cond_1, cond_2, cond_3 As FormatCondition
Set range_1 = Range("D5", Range("D5").End(xlDown))
Set cond_1 = range_1.FormatConditions.Add(xlCellValue, xlGreater, "=$D$5")
Set cond_2 = range_1.FormatConditions.Add(xlCellValue, xlLess, "=$D$5")
Set cond_3 = range_1.FormatConditions.Add(xlCellValue, xlEqual, "=$D$5")

With cond_1
.Interior.Color = vbCyan
.Font.Color = vbRed
End With

With cond_2
.Interior.Color = vbRed
.Font.Color = vbWhite
End With

With cond_3
.Interior.Color = vbBlue
.Font.Color = vbWhite
End With
End Sub

Highlight Cell Based on Value in Excel: 5 VBA Examples

We will show 5 Excel VBA examples to highlight cells based on value. We will apply the VBA codes of the following dataset.

1. VBA to Highlight Active Cell Based on Value

In the first example, we will check if an active cell satisfies criteria based on value and then highlight it.

Step 1:

• First, we go to the Sheet Name section at the bottom of each sheet.
• Press the right button of the mouse to get the Context Menu. Choose View Code from the menu.

Step 2:

• VBA window appears now. Choose the Module option from the Insert tab.

• Now, the VBA command module appears. We will write the VBA codes on that module.

Step 3:

• Now, select cell C7.
• Now, copy and paste the following VBA code on the module.

Sub hightlight_active_cell_value()
If ActiveCell.Value > 28 Then
   ActiveCell.Interior.Color = vbCyan
End If
End Sub

Step 4:

• Press F5 to run the code.

We set a condition in the VBA code. If the value of the active cell is greater than 28, the cell will be highlighted.

Code Explanation:

If ActiveCell.Value > 28 Then
   ActiveCell.Interior.Color = vbCyan
End If

Apply an If condition to check whether the active cell value is greater than 28. Define a color for active cells that fulfills the condition. Thus you can highlight an active cell based on the condition using VBA.

2. VBA to Highlight a Range of Cells Based on Cell Value

In this example, we will highlight a range of cells based on criteria. We will first check if the value is numeric or not using the VBA IsNumeric function.

Step 1:

• First, enter the VBA command module. For that, press the Alt+F11 keys.
• Copy the VBA code below on the module.

Sub hightlight_range_value()
  Dim range_1 As Range
  For Each range_1 In Range("C5:C9")
    If IsNumeric(range_1.Value) Then
      If range_1.Value > 28 Then
        range_1.Interior.Color = vbCyan
     End If
   End If
  Next range_1
End Sub

Step 2:

• Now, we will run the code by pressing the F5 button.

In this example, we put Range C5:C9 on the VBA code. Now, highlight the cells in the range which are greater than 28.

Code Explanation:

Dim range_1 As Range

Define a variable.

For Each range_1 In Range("C5:C9")

Use a for loop for each cell of Range C5:C9

If IsNumeric(range_1.Value) Then

Check if values are numeric or not.

 If range_1.Value > 28 Then

Check whether the value of the cell is greater than 28 or not.

range_1.Interior.Color = vbCyan

Set the color when the condition fulfilled.

Next range_1

Go to the next cell.

3. Highlight a Cell Based on Value with VBA FormatCondition Object

In this example, we will apply conditional formatting using Excel VBA FormatCondition Object.

Step 1:

• Hit the Alt+F11 keys to enter the command module.
• Write the following VBA on that module.

Sub hightlight_range_condition()
Dim range_1 As Range
For Each range_1 In Range("D5:D9")
If IsNumeric(range_1.Value) Then
range_1.FormatConditions.Add xlCellValue, xlGreater, Formula1:="=1200"
range_1.FormatConditions(1).Interior.Color = vbCyan
range_1.FormatConditions(1).StopIfTrue = False
End If
Next range_1
End Sub

Step 2:

• Hit the F5 button to run the code.

We apply a condition on a Range D5:D9. The condition is which cells value is greater than $1200.

Code Explanation:

range_1.FormatConditions.Add xlCellValue, xlGreater, Formula1:="=1200"

Use conditional formatting to check whether the value of the cell is greater than $1200 or not.

range_1.FormatConditions(1).Interior.Color = vbCyan

Set the color of that cell when conditional formatting is applied.

4. VBA to Apply Multiple Conditions Based on Single Value

In this example, we will apply conditional formatting with multiple conditions. Previously, we applied conditional formatting with a single condition. We will compare the value of cell D5 with the remaining cells of that column and highlight them.

Step 1:

• Press Alt+F11 and enter the VBA command module.
• Put the following VBA code on the module.

Sub multiple_conditional_formatting()
Dim range_1 As Range
Dim cond_1, cond_2, cond_3 As FormatCondition
Set range_1 = Range("D5", Range("D5").End(xlDown))
Set cond_1 = range_1.FormatConditions.Add(xlCellValue, xlGreater, "=$D$5")
Set cond_2 = range_1.FormatConditions.Add(xlCellValue, xlLess, "=$D$5")
Set cond_3 = range_1.FormatConditions.Add(xlCellValue, xlEqual, "=$D$5")

With cond_1
.Interior.Color = vbCyan
.Font.Color = vbRed
End With

With cond_2
.Interior.Color = vbRed
.Font.Color = vbWhite
End With

With cond_3
.Interior.Color = vbBlue
.Font.Color = vbWhite
End With
End Sub

Step 2:

• Run the code by pressing the F5 button.

We applied 3 conditions based on the value of cell D5. As a result, our cells have been highlighted with 3 different combinations.

Code Explanation:

Dim range_1 As Range
Dim cond_1, cond_2, cond_3 As FormatCondition

Define the variables.

Set range_1 = Range("D5", Range("D5").End(xlDown))
Set cond_1 = range_1.FormatConditions.Add(xlCellValue, xlGreater, "=$D$5")
Set cond_2 = range_1.FormatConditions.Add(xlCellValue, xlLess, "=$D$5")
Set cond_3 = range_1.FormatConditions.Add(xlCellValue, xlEqual, "=$D$5")

Set values of different variables with conditions.

With cond_1
.Interior.Color = vbCyan
.Font.Color = vbRed
End With

With cond_2
.Interior.Color = vbRed
.Font.Color = vbWhite
End With

With cond_3
.Interior.Color = vbBlue
.Font.Color = vbWhite
End With

Set the color of three conditions.

Read More: How to Highlight Active Row in Excel VBA

5. VBA to Apply Multiple Criteria to Highlight Cell Based on Multiple Values

In this example, we will apply multiple conditions to highlight cells. Previously, we applied a single-value condition.

Step 1:

• We will apply 2 conditions here. For this, press Alt+F11 and enter the command module.

Sub highlight_cell_multiple_condition()
Dim cell_1 As Range
Dim value_1 As Integer
Dim range_1 As Range
Set range_1 = Range("D5:D9")
For Each cell_1 In range_1
value_1 = cell_1.Value
Select Case value_1
    Case Is = 1500
    cell_1.Interior.Color = RGB(0, 255, 0)
    Case Is < 1200
    cell_1.Interior.Color = RGB(255, 0, 0)
End Select
Next cell_1
End Sub

Step 2:

• Press F5 to enter the command module.

We set two conditions. One is salary is equal to $1500 and the second one is less than $1200.

Code Explanation:

Dim cell_1 As Range
Dim value_1 As Integer
Dim range_1 As Range

Define the variable.

Set range_1 = Range("D5:D9")

Stores a range in range_1 variable.

For Each cell_1 In range_1

Apply a for loop on each cell of the range_1 variable.

value_1 = cell_1.Value

Stores the value of cell_1 variable on value_1 variable.

Select Case value_1

Apply case property on the value_1 variable.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Conclusion

In this article, we described how to highlight cells based on value with Excel VBA. I hope this will satisfy your needs. If you have further queries, feel free to mention them in the comment section.

Related Articles

• Excel VBA ColorIndex Chart
• Excel Color Index Numbers
• Excel VBA to Change Cell Background Color
• Excel VBA to Copy Cell Background Color
• How to Highlight Blank Cells in Excel VBA