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
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
Hi Howard:
Right click on the sheet tab and select “View Code”.
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.;-)
An email from an anonymous reader:
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.
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