Celebrate 'The VLOOKUP Book' birthday with us. Last day to get 50% discount on the e-book (31 October only).

Click here for details

Highlight Row & Column of Selected Cell using VBA

Posted on July 11th, 2012 in Excel Howtos , VBA Macros - 53 comments

When looking at a big table of analysis (or data), it would make our life simpler if the selected cell’s column and row are highlighted, so that we can instantly compare and get a sense of things. Like this:

How to Highlight row & column of a selected cell using Excel & VBA

 

Who doesn’t like a little highlighting. So lets learn how to do highlighting today.

Step 1: Identify the area for highlighting

This is simple, unless you are AUI (analyzing under influence). Lets assume that we are dealing with a range of cells in B4:I14

Step 2: Use 2 cells to capture the selected row & column details

Outside our highlight range, lets set aside 2 cells (E17 & E18 in this case) for keeping the details of which row & column needs to be highlighted.

We can call these cells selRow & selCol.

Step 3: Unleash the VBA magic

  • Right click on the sheet name & choose ‘view code’.
  • Choose Worksheet & Selection Change from the drop-downs.
  • Excel would add a blank Worksheet_SelectionChange() sub
  • Write the below lines of code.
    • [selRow] = Target.Row
    • [selCol] = Target.Column
  • Done. So much simpler than using floo network or transmogrifying muggles.

VBA code to capture selected cell's row & column

Step 4: Add conditional formatting to highlight selected cell’s row & column

Now that we know which row & column should be highlighted, it is a simple matter of switching on Excel’s highlighting charm – Conditional Formatting.
Conditional formatting rules to highlight row & column of a selected cell
Select the entire range (B4:I14) and go to conditional formatting > new rule

Select the rule type as Use a formula… and use a below rules.

  • =ROW(B4) = selRow
  • Apply formatting
  • Repeat the steps & this time use the rule =COLUMN(B4) = selCol

Step 5: Show off.

Incorporate this technique in to your dashboard or weekly report. Watch the socks knocked off your boss’. Bask in the glory. Repeat and enjoy.

Bonus Tip: Use similar technique to enhance user inputs

You can use similar idea to conditionally show messages on your worksheets. See this demo.

Enhance user inputs with message display in Excel - demo

I am not telling you how to do this. But I know you are awesome enough to figure this out.

Download Example File

Click here to download example file & understand how to use this technique.

Do you use highlighting techniques in your reports & analysis?

I always use conditional formatting & light-weight VBA to enhance my dashboards and analysis. Especially conditional formatting is almost a magical way to make stunning reports & show off things that are important.

What about you? Do you use these techniques often? what is your experience like? Please share your tips & ideas using comments. I am all ears.

Transmogrify your boring work to awesome – Check out!

Also see introduction to conditional formatting & VBA to understand to get the basics right.

For more potent magic, please consider joining our Online VBA Classes.  You are going to leave everyone spellbound.

Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

53 Responses to “Highlight Row & Column of Selected Cell using VBA”

  1. Peter says:

    Nice Chandoo,
    I’ve used this before too. Also used it for filling textboxes of an userform. When clicking on a cell the macro’s shows the userform and copies the values of cells around the selected one in the textboxes.
    Peter

    • Vetrina says:

      I am a bit AUI. :) I was confused at first, then I disected your example and figured it out.  Now I am going to use this in my reports.

      Thank you.  I may be joining a class soon.  I was excited that I did it.  But I hadn’t figured out the significance of the EPC% example. 

      Thanks that was fun, my brain is happy.

  2. Sawan says:

    Hi Chandoo,
    This is very cool, however I receive the below error and I am using Excel 2003.
     
    Run-time Error ’424′:
    Object required
    Thanks
    Sawan
     

  3. Keri says:

    Very useful tip!  Is there a way to apply this same formatting to a dynamic range of data? 

  4. Greg G says:

    “I am not telling you how to do this. But I know you are awesome enough to figure this out.”

    I’m *not* awesome at VBA, so yea.. I can’t figure it out.

  5. Dohsan says:

    I’ve used a similar method for highlighting rows on a sheet and ran into a few problems along the way.

    Users had issues copy and pasting cells (often the clipboard would be lost when a new selection was made)

    Also, as the sheet was quite complex it would force a recalculation on every select.

    The below is how I got it working in the end:

    I wanted the calculations to be off as standard (speeds the sheet up anyway). Also limited it to a certain range of cells and preventing the highlight if more than one cell was chosen.

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
     
    If Application.Calculation = xlCalculationAutomatic Then
        Application.Calculation = xlCalculationManual
    End If
     
    If Target.Row <= 8 Or Target.Row >= 700 Or Target.CountLarge > 1 Then Exit Sub
     
     
    Application.ScreenUpdating = False
    Application.EnableEvents = False
     
    With ThisWorkbook.Names(“ActiveRow”)
        .Name = “ActiveRow”
        .RefersToR1C1 = “=” & ActiveCell.Row
     
    End With
     
    Application.ScreenUpdating = True
    Application.EnableEvents = True
     
    End Sub 

    ActiveRow is the name range that the conditional formatting looks to.

    Target.CountLarge > 1 Then Exit Sub is used when multiple cells are chosen – it was originally Target.Count, but in excel 2010 the additional rows/columns caused it to implode if you selected the whole sheet, so countlarge was used to get around it.

    The only real issue is you lose the ability to use the undo function if you switch rows 

  6. Oleksiy says:

    Formula for conditional formatting:
    =AND(OR(ROW()=CELL(“row”),COLUMN()=CELL(“col”)),CELL(“row”)<=max_row,CELL(“row”)>min_row,CELL(“col”)>min_col,CELL(“col”)<max_col)
    Replace min_row and max_row, min_col and max_col with numbers, e.g. (5, 10, 2, 8) to highlight only B5:H10 range
    Bonus – formula to highlight (e.g. different text color) the selected cell:
    =AND(ROW()=CELL(“row”),COLUMN()=CELL(“col”))

  7. Fiona says:

    I’m not awesome in excel either — how do you do that cool trick at the end? 

    Can you use this technique without displaying the row and column number? I tried deleting it, but the two rows need to be there!  

  8. JohnC_UK says:

    Greg G

    The format for cell F23 has a custom number format of ;;;

    This means that the cell will not display anything by default

    The top conditional formatting entry changes the number format to General so the text is displayed.

    • Greg G says:

      That’s what happens when I try to look at things while I’m at work.  Never even looked at the format.

      That, however, doesn’t change the fact that I still suck at VBA lol

      • JohnC_UK says:

        I forgot to add that I think it’s a nice little trick.

        I love this site. Loads of tips of techniques that I’d probably not think of myself.

        Well done Chandoo for the good work!

  9. Mark says:

    I think this is a fantastic tip! This is functionality that Excel should have built in by default.
    I would actually like to turn this into a Macro that I could have available all the time in Excel so I would not have to set it up each time (although I realize it is not a lot of work). I suspect I would have to use some VBA code to open a dialogue box asking the user to select the area of data. I would also need to “hard code” in specific cells for the SelRow and SelCol statements. That would be easy enough to just put them out to a column to the far right that I know would not be used (XFD?). And then I would need to add it as a default macro and possibly even add a button to the Excel ribbon to activate it. I suppose I would also have to write some code to deactivate it as well so I could turn it off.
    Well, I’m terrible with VBA, but I suspect with enough Googling I could figure out how to make this work. Any thoughts or tips you may have about the process would be appreciated!
    Thanks in advance!
    Mark

    • JohnC_UK says:

      There are loads of things that I’d like Excel to have in by default.

      Surely by now Microsoft could have implemented temporary variables to be used within a formula to allow you the refernece that value in the same formula without the need to repeat the calculation or call the function again?

      It would also have massively improve performance in complex spreadsheets.

  10. Jeff Weir says:

    Chandoo…great post. One problem with this code is that it wipes out the undo stack. If you want to undo something, then you have to do it manually.
     
    Datapig did a similar trick at http://datapigtechnologies.com/blog/index.php/highlighting-the-active-row-and-column/ back in 2009.
    There were some great alternative approaches in the comments. I particulary liked this one from Rick Rothstein, that does not wipe the undo stack. (aside: I have no idea why some macros wipe the undo stack, but others dont.)
    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Union(Target.EntireColumn, Target.EntireRow).Select
    Target.Activate
    End Sub

  11. Jeff Weir says:

    Ahh, I see that the undo stack is only wiped if a code writes to the worksheet, as your code does. But Rick’s code I posted above doesn’t write to the worksheet…it just selects the row and column.
     
    Cool.

    • Chandoo says:

      Interesting point. I noticed that undostack is reset with this approach. After further investigation, I found a better solution. Instead of modifying selRow & solCol from VBA, we use two formulas  =cell(“row”) for selRow & =cell(“col”) for selCol. And then in our VBA, we just write target.calculate so that Excel would update the selRow & selCol values with latest row & column (ie target).

      See it in action here: http://img.chandoo.org/vba/highlight-row-column-v2.xlsm

      This preserves undostack and works just as awesomely.

      • JohnC_UK says:

        Thanks Chandoo – that is really interesting.

        From memory, UDfs can only update the cell containing the formula which I assume is so that they do not upset the Undo chain.

        As VBA can do anything that probably explain why Microsft just clears the undo chain. I’ll try and remember that target.calculate is an exception in future.

    • Chandoo says:

      Btw, I learned about cell(“row”) based technique from bobhc & debraj in our forums here: http://chandoo.org/forums/topic/vba-code-to-hightlight-the-current-position-modification

      • Brant says:

        I’ve been online for 10 minutes and already learned something new! I really like the cell(“row”/”column”) technique. I also didn’t realize you could hide the cell values. I’ve always set the font color to match the background to achieve that. Very nice..

  12. Vincent says:

    hi
    Chandoo,that’s great job!But I can’t figure out why the Conditional Formatting is “B4″,could you explain that?

    • JohnC_UK says:

      When you apply condition formatting to a range (in this case $B$4:$I$14) you write your formulas as if it was relating to the cell in the top left cell of the range (in this case B4).

      So, the top left cell is the “home” cell for all formulas. That is the base for the formula.

      You can still refer to cells up/down/left/right from the home cell and Excel will use those relative offstes for all other cells in the range.

      If your range was $F7:$G20 cell write your fomula as if it were cell F7 (top left).

  13. Dayanand says:

    Hi Chandoo,
     
    But how to add dynamic range in conditional formating..
     

    • JohnC_UK says:

      Hi Chandoo too

      I would like to know if this can be done.

      I looked at this a few weeks ago and all worked OK with named ranges but I wanted to use ranges where the names were in a cell to allow for a table driven approach. Tried everything I could think of to no avail! I wanted to do it without VBA macros.

      • Chandoo says:

        If you apply CF over a table, as the table grows, so does the CF. In Excel 2007 or 2010, when you goto conditional formatting > edit rules, you can see the range over which the CF is applied. You can edit this and change it to a dynamic named range or table. Either works. In fact, you can even use a table column name table[column] or pivot too.

        PS: When using table columns, you may have to define a name which points to table[col] and then use that name in CF range.

  14. Abdul Kader Salaymeh says:

    it is cool, i will be using this idea all over workbooks
    Many Thanks 

  15. Here’s how to do it with just the rollover method (no clicking on cells required!): 

    https://docs.google.com/open?id=0B1OBNnu3ZbL0UHdUVVVmbkRwdlk

    I’m far too obsessed with this technique :P 

  16. Matthew says:

    How do you create the limited view of the worksheet (A1:J30)?  Not sure what to call this or search for in the forums.

    • Dohsan says:

      If you select all the columns to the right of where you would like to finish, you can just hide them all. Similar can be down to all the rows below. 

  17. Owen says:

    Hi Chandoo
    Great work! This is a handy tool for analysts!
    This vba code now requires us to elect “Worksheet” and “SelectionChange” Does this mean the highlighting code only applies to the current active worksheet?
    Is it possible to write a vba code that applies to every single worksheet that the user opened and is active?
    Thank you

  18. Swapnil says:

    Awesome method, would be helpful for dashboard.
    Thanks a lot for sharing this.

  19. Sipher says:

    Crap. Not working for me on my sheet.
    Copied everything and only amended the cell #’s to match…yet no go.
     

  20. Zuber says:

    Why is selected row and Selected Column number is not changing ? and what formulas we need to put in there? thanks

  21. Zuber says:

    It worked thanks, but is there better alternative without doubleclicking the cell?

  22. [...] Highlight Row & Column of Selected Cell using VBA | Chandoo.org – Learn Microsoft Excel Online [...]

  23. William says:

    AWESOME!!! looks so cool! Everything worked for me! I’m gonna show off at the office tomorrow

    thank you

  24. Mark says:

    Great tool. I have it working well, but I had to cheat. I did everything you showed above, that didn’t work. I downloaded v2 and dissected it next… I put the same code on the worksheet and added the same conditional formatting and replicated the two row and col cells on the sheet but it just wouldn’t work. I have no idea why. In the end, I just took your sheet, moved the two row and col cells out of the way and pasted my stuff onto the worksheet and it works fine. Really though, I’d love to implement this tool on many sheets but I can’t figure out what I’m doing wrong. I see no difference between the v2 I downloaded and the one I make from scratch copying all that’s in v2 but mine just won’t work. If I could send you the sheet I made that would be great. I can only imagine there is some hidden code somewhere but I cannot find it.
    Thanks!

  25. Ben says:

    I have many many tables on a worksheet. Can anyone think of a way to make this work just within the table with the current active cell?

    Thanks Chandoo and everyone else who contributes such invaluable pearls of wisdom!

  26. Laurent says:

    Fine, but if the formula calculation method is set to ‘Manual’, this is not working with the method Target.Calculate
    The workaround is to force the Calculation method to Automatic just for the operation :

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Dim iState As Integer
    Dim bSaved As Boolean

    If Application.CutCopyMode = False Then
    bSaved = ThisWorkbook.Saved
    iState = Application.Calculation
    Application.Calculation = xlCalculationAutomatic
    Target.Calculate ‘ Enable to highlight the active row with conditional formatting
    Application.Calculation = iState
    ThisWorkbook.Saved = bSaved
    End If

    The only drawback is that when you are in copy mode, the row and/or column are not updated

  27. dockhem says:

    Hallo,
    Can you, please, tell me how can i high light excel rows and column of selected cell in excel 2010 and 2013.
    Thanks

  28. Ahmed says:

    HI Chandoo,

    This is just great!

    I’ve been reading all of your posts for a year or so now! and they are insightful and very helpful.

    Thanks

Leave a Reply