How to Highlight Rows in VBA(Quick & Easy Guide)

In this tutorial you will learn How to Highlight Rows in VBA.

To Highlight Rows in VBA you can use the following methods :

Approach 1: Highlighting Active Rows

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

This specific macro will highlight the active row.

Example 1:

Assuming we have currently selected the second Row.

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

We can use the below macro to higlight the currently active row.

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

All other rows are unaffected, but you’ll see that Second row has every cell highlighted.

Step 1 : Open the excel spreadsheet Where you need to Highlight rows

Step 2 : Right Click on the Sheets and Select “View Code“

Step 3 : Select Worksheet from the drop-down

Using the drop-down Change from General to Worksheet .This step will automatically add one private subject procedure for the SelectionChange event.

Step 4 : Copy the below code

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

Step 5 : Run the Code

Click on Run menu and Select “Run Sub/UserForm”

Approach 2 : Highlighting Specific Row

To highlight Specific Rows you can use the below macro

Sub HighlightSpecificRow()
Rows("5:5").Interior.Color = vbGreen
End Sub

The above macro will highlight Row number 5 in the currently active Sheet.

Example 2 :

Assuming you only want to Highlight row 5 in the current sheet.

You can use the following macro to achieve the same.

Sub HighlightSpecificRow()
Rows("5:5").Interior.Color = vbGreen
End Sub

After executing the above macro ,you will get the following output :

You will notice that each cell in row 5 is highlighted and the rest of the rows are left untouched .

Approach 3 : Highlighting Multiple Specific Rows

To Highlight Multiple specfic rows you can use the following macro .

Sub HighlightSpecificRows()
Range("1:1,5:5,7:7,9:9").Interior.Color = vbYellow
End Sub

The above macro will highlight the 1st,5th,7th and 9th rows in the currently active Sheet .

To highlight a specific range for example between row 1 to row 8 you can use the following function with values “1:8”, Range(“1:8”) in the macro.

Let’s go through an example to see the above formula in practice.

Example : 3 Highlighting Multiple Specific Rows

Assuming you only want to highlight rows 1,5,7 and 9 in you curent Sheets.

You can use the following macro to achieve the same .

Sub HighlightSpecificRows()
Range("1:1,5:5,7:7,9:9").Interior.Color = vbYellow
End Sub

You will notice that each cell in row 1,5,7 and 9 is highlighted and the rest of the rows are left untouched

The highlight color in each example is Green (vbGreen), but you may also use other colors like vbRed, vbYellow, vbBlue, etc.

How to Highlight Rows in VBA(Conlcusion)

We hope this article on How to Highlight Rows in VBA was useful.