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

This article illustrates how to highlight the active row using Excel VBA. Assume you have a large dataset with many columns. Then you may lose track of the row while finding data along the rows. But you can highlight the active row using Excel VBA to avoid this annoying problem. You can do it without VBA too. Follow the article to learn how to highlight active row/rows in a single worksheet or all worksheets.

Download Practice Workbook

You can download the practice workbook from the download button below.

2 VBA Codes to Highlight Active Row in Excel

Here we will discuss the ways to highlight the active row in a single worksheet and all worksheets in Excel using VBA. So let’s begin.

1. VBA to Highlight Active Row in Single Worksheet

Follow the steps below to highlight the active row in a single worksheet using VBA in Excel.

📌 Steps:

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

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

• Now copy the following code and paste it inside the subject procedure.

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

• Next, go back to the worksheet and select any cell to see the following result.

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

• Now 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

• After that, the active row will be highlighted as follows.

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

.EntireColumn.Interior.ColorIndex = 40

Read More: How to Highlight a Row in Excel (2 Effective Ways)

2. VBA to Highlight Active Row in Entire Excel Workbook

Now follow the steps below to highlight the active row in all sheets of the workbook without copying the code to each sheet.

📌 Steps:

• First, open the VB editor by pressing ALT + F11. You can also do it from the Developer tab. Then double-click on This Workbook below the sheet names. Next, select Workbook and then the SheetSelectionChange event using the dropdowns. After that, a private subject procedure will be inserted for the event automatically. Delete all other procedures from that code module.

• After that, paste the same code used earlier inside this procedure.

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

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

Read More: How to Highlight Row If Cell Is Not Blank (4 Methods)

How to Highlight Active Row in Excel without Using VBA Code

One noticeable limitation of using VBA to highlight the active row in excel is that it removes all fill colors applied manually (except conditionally formatted). Follow the steps below to highlight the active row in Excel without using VBA to avoid this.

📌 Steps:

• First, select the entire worksheet by clicking on the arrow at the upper left corner of the first cell. Then select Home >> Conditional Formatting >> New Rule. After that, the New Formatting Rule dialog box will appear.

• Now select ‘Use a formula to determine which cells to format’ as the Rule Type. Then enter the following formula in the formula box. Next click on Format, pick a Fill Color and click OK.

• Now select any cell to see the active row highlighted as follows.

• However, you need to 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.
• You can apply another conditional formatting using the following formula to highlight the active column too.

Read More: How to Highlight Row Using Conditional Formatting (9 Methods)

Things to Remember

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

Conclusion

Now you know how to highlight the active row in Excel with or without using VBA. Do you have any further queries or suggestions? Please let us know in the comment section below. Please visit our ExcelDemy blog to explore more about Excel. Stay with us and keep learning.

Related Articles

• Highlight Entire Row with Conditional Formatting in Excel
• How to Highlight Every 5 Rows in Excel (4 Methods)
• Highlight Row If Cell Contains Any Text
• How to Do Conditional Formatting Highlight Row Based On Date
• Highlight Duplicate Rows in Excel (3 Ways)
• How to Highlight Every Other Row in Excel (3 Easy Methods)