Automatically Highlight Active Row in Excel

I use Excel… a LOT, and for a very long time! With that comes the inevitable… my eyes aren’t as young as they used to be… plus, we tend to have a TON of data in our Excel file, sometimes making it harder to know which data is on the row that I am looking at.

I know, you can use themes, or highlight the row manually, but I want something dynamic. I want to highlight the active row (the row where my cursor is)… and I am greedy, I want it to change every time I change rows.

I personally think this should be a feature built into Excel, but I cannot find it anywhere – if you know of a different way, please post a comment below.

I’ve put together a few steps to automatically highlight my active row. Read on to see these steps and maybe put it to use for you!

To highlight the row you are working on, there are a few steps you need to go through for the file. In addition, you should know in advance, the file will need to become a macro file (xlsm) because we will be adding a tiny bit of VBA – DON’T WORRY!! This is easy, so keep reading!

Step 1: Define a Name Range to use in VBA

A named range is needed – to do this, just go to Formulas/Define Name. I used ‘HighlightRow’ as my name. You can use whatever you would like, but it must be used later, so be sure to be consistent.

Also, the ‘REFERS TO’ box must be changed to ‘=1’.

Step 2: Add Conditional Formatting

In this step, we will need to add the conditional formatting that will be used in our VBA.

Click the ‘select all cells’ button in the top left of your spreadsheet.

Next, go to the Home Tab, then go to conditional formatting, and add a new rule.

When the new Formatting Rule window opens, choose ‘Use a Formula’ and then define the formula. The formula will be ‘=Row(a1)=HighlightRow’ – where “HighlightRow” is the name of the defined range in Step 1. Then click the format button.

In the format cells window, switch to the fill tab, and choose the color you want to use as the color to highlight the active row.

Then click OK on the Format Cells window, and OK on the New Formatting Rule window. At this point, Row 1 should be highlighted with the color you selected. But that’s not the complete end result that we want… we want the row to change when our active row changes. This is where we bring in a little VBA.

Step 3: Add VBA

For this step, you will need the Developer tab available in your ribbon. If it is not available, you can add it by going to File/Options/Customize Ribbon and turn on the developers tab.

In the developer tab, click Visual Basic. This will open the Visual Basic Editor. Select the workbook you are working on, and double click the sheet you want this code to work with… When you double click, the code window will open, change the drop down from General to Worksheet.

Once you select worksheet from the drop down, be sure that the second drop down shows ‘SelectionChange’ is selected, if not, use the drop down and select it.

The default code will look like this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

Highlight the default code and replace it with this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With ThisWorkbook.Names("HighlightRow")
.Name = "HighlightRow"
.RefersToR1C1 = "=" & ActiveCell.Row
End With
End Sub

In the code above, be sure that the value in quotes (“HighlightRow”) matches your named range that you defined in step 1.

Close the VBA window and go back to excel. If you followed the steps closely, you should be able to change active cells and have them automatically highlight! VERY VERY COOL!!

Step 4: Save as xlsm file

If you do not save this Excel file as an xlsm file, the code will only work this one time. In order to keep the VBA, save it as an XLSM file (Macro) and it will work every time you open the file up! Simply go to File, Save as, and choose the drop down for macro files.

That’s it! You are all set! YOU DID IT!!! Now, go impress your friend!!! πŸ™‚

The image right above this line is a GIF file of this change in action (if you do not see it, try opening this page up on a desktop) – I want to make sure to thank my friend, and fellow MVP, Jen Kuntz for motivating me to use a GIF – it’s my first time, thanks Jen!! Jen adds to her own blog EVERY TUESDAY – be sure to check it out here.

Pretty Cool huh? I use this a lot and I hope you enjoy it!

I learned how to do this by combining a few things I found across the web… I do not write VBA – I did it, so you can too!!


Thanks for reading!

Shawn Dorward

Microsoft MVP, Business Applications

LifeHacks365.com | LinkedIn | Twitter

8 thoughts on “Automatically Highlight Active Row in Excel”

  1. Shawn, your steps are very easy to follow. I do believe that I was able to follow them to the letter, however I keep getting a VBA erro message saying that the subscript is out of range even though the HighlightRow is what I named the defined range in step 1. What am I doing wrong?

    1. Lori – if you see Steve’s comment below, he is pointing out EXACTLY what is causing your error – I reposted the code in my blog so trying to copy and paste it should work now. Otherwise, replace the quotes per Steve’s note and remove the spaces around the = sign πŸ™‚ Please post back and let us know if this works!

  2. Hi, Shawn! I am shamelessly using this method to show to our GPUG Chapter. There are a couple things to watch out for with the Sub Worksheet_SelectionChange() code above.

    1. If you copy and paste the code directly from the web page into your VBA editor, the quotation marks surrounding the “HighlightRow” and the “=” sign are ACTUAL open and close quotation marks, which VBA doesn’t recognize. You have to replace them all with the plain-jane ” mark.

    2. In the pasted code in the VBA editor, the “=” winds up actually being ” = “. There’s a space before and after the = sign. VBA doesn’t like that either.

    Once those two things are taken care of in the code, the highlight works!

      1. That’s just the ticket, Shawn!

        By the way, during my presentation of this method at our GPUG Green Bay Chapter meeting yesterday, one of our members pointed out that one can always click on the row number to highlight the whole row. While that’s true, I replied, as soon as you click on any cell in that row, the highlight goes away. Your method leaves the highlight intact so that you don’t lose your place! And it works from anywhere in the sheet, mouse or keyboard.

        The only shortcoming is that, apparently, one must activate this code for each sheet in a workbook. Is this the case?

        Thanks for making the correction… and thanks even more for this cool tip!

  3. This hack is life! I can’t express how greatful I am to have found this and how easy it was to complete. You ROCK!

Leave a Reply