Highlight row and column of active cell

By default, when the user selects a cell, Excel highlights the row and column by changing the color of the associated row and column headers. This tip shares multiple ways to highlight the row and column in more obvious ways as well as a way to highlight the cell in a specific column in the same row.


The emphasis is on the use of conditional formatting to accomplish the task. The minimal VBA code required to make it work is the same single executable statement for all of the different highlighting options!

For a version in a page by itself (i.e., not in a scrollable iframe as below) visit http://www.tushar-mehta.com/publish_train/xl_vba_cases/0121%20highlight%20row%20and%20col%20of%20selected%20cell.shtml

Tushar Mehta

5 Comments

  1. I’m a newbie. Please explain exactly how do I get to “the code module of the worksheet containing the conditional formatting” as instructed in the following section?:

    Automating the process

    A very simple one-line VBA routine that will automate the recalculation is shown below. An additional benefit of this code is that it does not clear out the undo stack. Consequently, one can use undo even with this code active.

    In the code module of the worksheet containing the conditional formatting:

    Option Explicit

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Target.Calculate

    End Sub

  2. Brilliant. Thank you so, so, much Tushar. I’ve been looking for months for a solution to the format deletion and undo/undo problems. You are the ONLY person I have found who has come up with a workable solution. I have to remember to put the cursor in the top left for printing but that’s fine. I just wish conditional formatting in excel 7 had line thickness option!!!!

    A thumb on the nose to Microsoft who thought no one could break their deliberate bugs – which they obviously add in to force people to buy more versions of their software! (Watch out Google doesn’t shunt you off their search engine.;-)

  3. An email from an anonymous reader:

    Ok this is good, sure, but it leaves me with a substantially slower excel file as it calculates every time I select a cell. I’d like excel to just calculate the selected cell, that would be a time saver. But how?

    Looking forward to your suggestions.

    Actually, the code already does just that, i.e., it calculates only the selected cell. So, it is already optimized to minimize the overhead on the Excel environment.

  4. Awesome job! Your instructions were detailed and easy to follow. I’m not sure why Excel removes the selection highlight when Excel is not the active window (thanks, Microsoft), but with your instructions, I have an effective means to keep the data highlighted as I click away from Excel.

    Dan

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>