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

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 apply the VBA codes on the following dataset.

Method 1 – VBA to Highlight the Active Cell Based on Value

Steps:

• Go to the Sheet Name section at the bottom of each sheet.
• Right-click to get the Context Menu.
• Choose View Code from the menu.

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

• The VBA command module appears.

• Select cell C7.
• 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

• 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

We applied an If condition to check whether the active cell value is greater than 28. The code defined a color for active cells that fulfills the condition.

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

We will check if the value is numeric using the VBA IsNumeric function.

Steps:

• Enter the VBA command module via Alt + F11.
• Copy the VBA code below into 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

• Run the code by pressing the F5 button.

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

Code Explanation:

Dim range_1 As Range

Defines a variable.

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

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

If IsNumeric(range_1.Value) Then

Checks if values are numeric or not.

 If range_1.Value > 28 Then

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

range_1.Interior.Color = vbCyan

Sets the color when the condition fulfilled.

Next range_1

Goes to the next cell.

Method 3 – Highlight a Cell Based on Value with the VBA FormatCondition Object

Steps:

• Hit Alt + F11 to enter the command module.
• Insert the following VBA in 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

• Hit the F5 button to run the code.

• We applied a condition on a Range D5:D9. We’ll highlight cells with values greater than $1,200.

Code Explanation:

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

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

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

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

Method 4 – VBA to Apply Multiple Conditions Based on a Single Value

We will compare the value of cell D5 with the remaining cells of that column and highlight them.

Steps:

• Press Alt+F11 to enter the VBA command module.
• Put the following VBA code in 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

• Run the code by pressing the F5 button.

• We applied 3 conditions based on the value of cell D5. Our cells have been highlighted with 3 different colors.

Code Explanation:

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

Defines 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")

Sets the 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

Sets the colors of the three conditions.

Read More: How to Highlight Active Row in Excel VBA

Method 5 – VBA to Apply Multiple Criteria to Highlight a Cell Based on Multiple Values

Steps:

• We will apply 2 conditions.
• Press Alt + F11 to enter the command module.
• Paste in the following code.

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

• Press F5 to run the code.

• We set two conditions. One is salary is equal to $1,500 and the second one is less than $1,200.

Code Explanation:

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

Defines the variable.

Set range_1 = Range("D5:D9")

Stores a range in range_1 variable.

For Each cell_1 In range_1

Applies 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

Applies the case property on the value_1 variable.

Download the Practice Workbook

Related Articles

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