How to Highlight Cells in VBA (With Examples)


You can use the following methods in VBA to highlight cells:

Method 1: Highlight Active Cell

Sub HighlightActiveCell()
ActiveCell.Interior.Color = vbYellow
End Sub

This particular macro will highlight the currently active cell with a yellow background.

Method 2: Highlight Range of Cells

Sub HighlightRange()
Range("B2:B10").Interior.Color = vbYellow
End Sub

This particular macro will highlight each cell in the range B2:B10 with a yellow background.

Method 3: Highlight Range of Cells Based on Criteria

Sub HighlightRangeBasedOnCriteria()

  Dim rng As Range
  
  For Each rng In Range("B2:B10")
  
      If rng.Value > 20 Then
        rng.Interior.Color = vbYellow
      End If
      
  Next rng
  
End Sub

This particular macro will highlight each cell in the range B2:B10 that has a value greater than 20.

The following examples show how to use each method in practice with the following dataset in Excel:

Example 1: Highlight Active Cell

Suppose we currently have cell B3 selected.

We can create the following macro to highlight this active cell:

Sub HighlightActiveCell()
ActiveCell.Interior.Color = vbYellow
End Sub

When we run this macro, we receive the following output:

VBA highlight active cell

Notice that cell B3 is highlighted and all other cells are simply left untouched.

Example 2: Highlight Range of Cells

Suppose we would like to highlight each cell in the range B2:B10.

We can create the following macro to do so:

Sub HighlightRange()
Range("B2:B10").Interior.Color = vbYellow
End Sub

When we run this macro, we receive the following output:

VBA highlight range of cells

Notice that each cell in the range B2:B10 is highlighted and all other cells are left untouched.

Example 3: Highlight Range of Cells Based on Criteria

Suppose we would like to highlight each cell in the range B2:B10 that has a value greater than 20.

We can create the following macro to do so:

Sub HighlightRangeBasedOnCriteria()

  Dim rng As Range
  
  For Each rng In Range("B2:B10")
  
      If rng.Value > 20 Then
        rng.Interior.Color = vbYellow
      End If
      
  Next rng
  
End Sub

When we run this macro, we receive the following output:

VBA highlight cells based on criteria

Notice that each cell in the range B2:B10 with a value greater than 20 is highlighted and all other cells are left untouched.

Additional Resources

The following tutorials explain how to perform other common tasks in VBA:

VBA: How to Apply Conditional Formatting to Cells
VBA: How to Apply Conditional Formatting to Duplicate Values
VBA: How to Find Value in Column

Leave a Reply

Your email address will not be published. Required fields are marked *