Table of Contents
Highlighting rows in Excel with VBA is a great way to quickly identify and track data. You can use VBA to loop through the rows in a worksheet and set the cell background color to a color of your choice. This makes it easier to visually identify specific rows of data and can be especially useful when analyzing large datasets. Additionally, you can use VBA to add conditional formatting to quickly highlight rows that meet specific criteria.
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:
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.