How to Highlight Rows in VBA (With Examples)


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

Method 1: Highlight Active Row

Sub HighlightActiveRow()
ActiveCell.EntireRow.Interior.Color = vbYellow
End Sub

This particular macro will highlight the currently active row.

Method 2: Highlight Specific Row

Sub HighlightSpecificRow()
Rows("4:4").Interior.Color = vbYellow
End Sub

This particular macro will highlight row 4 in the current sheet.

Method 3: Highlight Several Specific Rows

Sub HighlightSpecificRows()
Range("2:2,4:4,6:6,8:8").Interior.Color = vbYellow
End Sub

This particular macro will highlight rows 2, 4, 6, and 8 in the current sheet.

Note: To highlight all rows between 2 and 8, you can type Range(“2:8”) instead.

The following examples show how to use each method in practice.

Example 1: Highlight Active Row

Suppose we currently have cell B3 selected.

We can create the following macro to highlight each cell in the currently active row

Sub HighlightActiveRow()
ActiveCell.EntireRow.Interior.Color = vbYellow
End Sub

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

Notice that each cell in row three is highlighted and all other rows are simply left untouched.

Example 2: Highlight Specific Row

Suppose we would like to highlight row four only.

We can create the following macro to do so:

Sub HighlightSpecificRow()
Rows("4:4").Interior.Color = vbYellow
End Sub

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

Notice that each cell in row four is highlighted and all other rows are simply left untouched.

Example 3: Highlight Several Specific Rows

Suppose we would like to highlight rows 2, 4, 6, and 8 in the current sheet.

We can create the following macro to do so:

Sub HighlightSpecificRows()
Range("2:2,4:4,6:6,8:8").Interior.Color = vbYellow
End Sub

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

VBA highlight several specific rows

Notice that rows 2, 4, 6, and 8 are all highlighted and all other rows are left untouched.

Note: In each example we chose to use yellow (vbYellow) as the highlight color, but you can choose a different color such as vbRed, vbGreen, vbBlue, etc.

Additional Resources

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

VBA: How to Highlight Cells
VBA: How to Apply Conditional Formatting to Cells
VBA: How to Apply Conditional Formatting to Duplicate Values

Leave a Reply

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