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.

highlight active row with excel vba


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.

View Code of Worksheet

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

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

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

click on cell to highlight the row

  • It will highlight multiple rows too. 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.
  • 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

code to unhighlight the active cell

  • After that, 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 also.
.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. Besides you can copy the code in each sheet to apply the highlighting effect in those worksheets.

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.

highlight active row in workbook

  • After that, 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

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.

add new conditional formatting rule

  • 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.
=CELL("ROW")=CELL("ROW",A1)

formula to highlight active row

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

highlight active row with conditional formatting

  • 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.
=CELL("COL")=CELL("COL",A1)
Tips: You can press SHIFT + Space and CTRL + Space to temporarily highlight the active row and the active column respectively.

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

Md. Shamim Reza

Md. Shamim Reza

Hello there! This is Md. Shamim Reza. Working as an Excel & VBA Content Developer at ExcelDemy. We try to find simple & easy solutions to the problems that Excel users face every day. Our goal is to gather knowledge, find innovative solutions through them and make those solutions available for everybody. Stay with us & keep learning.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo