fbpx
Search
Close this search box.

Highlight Row & Column of Selected Cell using VBA

Share

Facebook
Twitter
LinkedIn

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.

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Excel School made me great at work.
5/5

– Brenda

Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

Weighted Average in Excel with Percentage Weights

Weighted Average in Excel [Formulas]

Learn how to calculate weighted averages in excel using formulas. In this article we will learn what a weighted average is and how to Excel’s SUMPRODUCT formula to calculate weighted average / weighted mean.

What is weighted average?

Wikipedia defines weighted average as, “The weighted mean is similar to an arithmetic mean …, where instead of each of the data points contributing equally to the final average, some data points contribute more than others.”

Calculating weighted averages in excel is not straight forward as there is no built-in formula. But we can use SUMPRODUCT formula to easily calculate them. Read on to find out how.

70 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
     

    • Brant says:

      Nice tip.
      I really like the user input prompt idea as well.

      Sawan,
      In your VBA Selection Change event code, write:

      Range("SelRow") = Target.Row
      Range("SelCol") = Target.Column

      Brant

      • Jenny says:

        Brant, tried the Range ("SelRow")=Target.Row, but got another error

        Run-time error '1004'
        Method 'Range' of object '_worksheet' failed 

        can you please help advise? many thanks! 

      • Michelle says:

        I also received runtime error 424 then made the suggested changes, and now receive runtime error '1004'. Any ideas?

      • Chandoo says:

        @Sawan, Jenny & Michelle... Can you try this file:

        http://img.chandoo.org/vba/highlight-row-column-v2.xlsm

        • Jenny says:

          @chandoo

          Thanks for the revision, I no longer have the runtime error. BUT, after applied the conditional formatting, nothing happened when I click the cell. Not sure where I may lost. Any comments? Thanks again in advance!

        • Divyan says:

          Dear Chandoo,
          I have tried both ways as you explained here
          Code-1
          ---------
          SelCol=Target.Column
          SelRow=Target.Row

          Code - 2
          ----------
          Target.Calculate

          And did the format as you mentioned multiple times, deleted the format and Redid the same..Still not working. When I was using Code-1 It was giving me Error-424 but while using the Code-2 - No Error but not highlighting as expected. Kindly help me further.Thank you

        • Dinesh says:

          I have downloaded your above linked xL in that i have added new sheet and followed the same steps to high lite the row column but its not working and there is no error too. can u pls help me to fix that.?

          • Dinesh says:

            DEar Chandoo,
            I have downloaded your above linked xL, in that i have added new sheet and followed the same steps to high lite the row column but its not working in my new sheet and there is no error too. can u pls help me to fix that.?
            How can i sent that XL sheet to you.? Thanks in advance.

    • LinhNguyen says:

      Do step 2 again, you will solve this problem

  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 😛 

  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

  29. Jody says:

    this is awesome but i have a question and forgive me but i am not expert in this area yet. I would like this to just highlight the row seelcted, as you have here, but instead of the entire column, how can i get it to just highlight the header for the column? I have this table that is range A:R and row 2 is my table header row. I want the user to know which column they are updating by just seeing the header highlighted and not the entire column. is this possible? 🙂
    Thanks for your help!

    • Hui... says:

      @Jody
      Using the example
      Select the Range: B5:I14
      Conditional Formatting, Clear Rules, Clear Rules from Selected Cells
      Enjoy

      To apply it to a new Range, Only apply it to the Header Row

  30. Raze says:

    The author forgot to tell you that E17 and E18 are actually NAMED locations.

    Open the name manager and add
    E17 as celRow
    and E18 as celCol

  31. Murugan says:

    Dear Chandoo

    I need your help - can you please create one excel macro add on or xlam file
    where auto highlighting rows and columns while moving cursor automatically.
    Thanks lot.

    Regards
    Murugan.V S

  32. comando_sur says:

    I'm new in vba. Please need help to modify the below macro only to highlight the entire row within a table (table call 'summary') and not the entire row in the spreadsheet. hope this make sense

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim str$
    On Error Resume Next
    With Target
    str = IIf(.Count = 1, .Address & ", " & .EntireRow.Address, Target.Address)
    End With
    Range(str).Select
    On Error GoTo 0
    End Sub

  33. Rahul says:

    I tried the trick in a financial model (having multiple excel sheets). Does the size and complexity of model affects the highlighting effect (as explained above). The highter was erratic when the sheet i chose was a part of the entire financial model workbook, however, as soon as I separated the excel sheet (standalone in a separate workbook) from the same model the highlighter was working absolutely fine.
    Please help.

  34. Mohammad Ishaq says:

    The micro to highlight row and column.But i thing is missing that it is only work on selected range.
    Is it possible to make it dynamic,i mean that when new data entry the micro automatically highlight the new row or column.

    Thanks

  35. Qudsia says:

    Hi Chandoo. Thanks for the solution. But, please clarify in Step#2 to name the two cells outside the range 'selRow' and 'selCol' using Define Name feature in Formula tab. Otherwise it sounds like you are just asking the users to make an assumption instead of taking an action. This is what's giving the 'object not defined' error for people (as it did for me). Unless those cells are named ranges, VBA doesn't have an object to work with to enter row and column numbers of selected cell.

    • Chris says:

      Thanks Qudsia. I wish this could be at the top of the comments. I only found it by chance and now the macro works perfect.

      • Chris says:

        Well I thought it worked fine. It highlights the row above the one I select. The value in selRow is right. selCol works fine. Can't figue what is wrong. 🙁

Leave a Reply