How to Highlight an Active Row in Excel VBA (2 Examples)

Method 1. Using VBA to Highlight an Active Row in a Single Worksheet

Steps:

Right-click on the sheet tab and select View Code. Alternatively, you can press ALT + F11 and double-click on the sheet name in the VB editor.

View Code of Worksheet

Select Worksheet using the first dropdown in the code module. This will automatically insert a private subject procedure for the SelectionChange event.

Worksheet SelectionChange Event

Copy the following code and paste it inside the subject procedure.

Cells.Interior.ColorIndex = xlNone
With Selection
.EntireRow.Interior.ColorIndex = 40
End With

code for highlighting active row

Return to the worksheet and select any cell to see the result.

click on cell to highlight the row

It will highlight multiple rows. Just select cells from multiple rows to see the following result.

highlight multiple rows

Tips: You can use the CTRL key to select non-adjacent cells or ranges.

Copy the following code and paste it below the End With statement to highlight the active row except the selected cell.

Selection.Interior.ColorIndex = xlNone

code to unhighlight the active cell

The active row will be highlighted as follows.

unhighlighted active ranges

You can add the following code before the End With statement to highlight the active column.

.EntireColumn.Interior.ColorIndex = 40

highlight the active column

Tips:

You can change the color-index value to highlight the active cell with a different color. You can copy the code on each sheet to apply the highlighting effect in those worksheets.

Method 2 – Using VBA to Highlight an Active Row in an Entire Excel Workbook

Steps:

Open the VB editor by pressing ALT + F11. You can also do it from the Developer tab.
Double-click on This Workbook below the sheet names.
Select Workbook and the SheetSelectionChange event using the dropdowns.
A private subject procedure will be inserted for the event automatically.
Delete all other procedures from that code module.

highlight active row in workbook

Paste the same code used earlier inside this procedure.

Cells.Interior.ColorIndex = xlNone
With Selection
.EntireRow.Interior.ColorIndex = 40
End With

code to highlight active row in workbook

Whenever you go to a worksheet, the active row in that sheet will be highlighted automatically.

How to Highlight an Active Row in Excel without Using VBA Code

Steps:

Select the entire worksheet by clicking on the arrow at the upper left corner of the first cell.
Select Home >> Conditional Formatting >> New Rule.
The New Formatting Rule dialog box will appear.

add new conditional formatting rule

Select ‘Use a formula to determine which cells to format’ as the Rule Type.
Enter the following formula in the formula box.
Click on Format, pick a Fill Color, and click OK.

=CELL("ROW")=CELL("ROW",A1)

formula to highlight active row

Select any cell to see the active row highlighted as follows.

highlight active row with conditional formatting

Press F9 to refresh the selection when you click another cell. Otherwise, the earlier row will remain highlighted. You can also double-click and click away to refresh the worksheet.
Using the following formula to highlight the active column, you can apply another conditional formatting.

=CELL("COL")=CELL("COL",A1)

Tips: You can press SHIFT + Space and CTRL + Space to highlight the active row and the active column temporarily.

Things to Remember

Don’t forget to save the workbook as a macro-enabled workbook.
You must copy the code to each sheet using the Worksheet SelectionChange event.
You must insert the Workbook SheetSelectionChange event and copy the code to apply the highlighting effect to all worksheets.
Change the color-index values in the code to apply different highlighting colors.
While using conditional formatting, you must refresh the worksheet (press F9) to highlight the newly active row.
It is better not to apply the worksheet and workbook events to the same sheet to highlight the active row.

Download the Practice Workbook

You can download the workbook to practice.

Highlight Active Row.xlsm