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.
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