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:

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:

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:

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