Crosshairs for quick cell location - temporary highlight columns and rows intersecting cell

[Legacy 277976]

Does anyone know a way to make excel show a "cross-hair" on the cell you have selected? Something that highlights the fill of the rows and the columns intersecting the cell you have last clicked on (nothing permanent, I don't want to be changing the fill of other cells), more as a means to make it easier to find where you are working quickly. In my case, I am quickly switching between two screens and multiple spreadsheets, the short period of time it takes to get re-oriented on the spreadsheet tends to add up and generally contribute to my workday malaise.

I know that by default Excel shows which row and column you have selected, but sometimes my spreadsheet is vast and my magnification is low and it's still hard to intuitively find the cell you are last working on. The latest version of SnagIt (the Screenshot Software) has a cool way of doing this where it presents crosshairs from the cursor and darkens everything on the screen excel for the box you left click, drag and draw. Publisher and Word do this to some degree but with the scaling lines on the margin rulers.

 

[Tyron]

Hey Guys,

Guess no one remembers?

later

Ty

 

[Worf]

I never heard of a cross hair shortcut; maybe there was a macro behind it...

 

[Shellecj]

Yes

I came across this trick a couple years ago when searching for a similar solution. BTW, this solution does not effect the Undo Call Stack so you can still hit Undo/CTRL+Z UNlike other macros.

In the workbook module (ALT+F11) enter in this code:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

    Target.Calculate
    
End Sub

Save and then go back into the worksheet and go to the Conditional Formatting options and choose to enter a formula.

in the formula enter this: =ROW()=CELL("row") and then choose the desired formatting. Make sure this formula is applied to your entire range and make sure to absolute it.

Then enter another rule (via formula) and enter this formula =COLUMN()=CELL("col") and likewise set your desire formatting.

Similarly, you can also enter this code is just the specific worksheet module that where your data is instead of placing code in the This Workbook Module. For this option, choose the appropriate sheet module that your data is in and then use the macro code below.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 Target.Calculate
End Sub

 

[Tyron]

Hey Worf,

Don't think so as most of the people at my old job were anti-macro back then. I was the first to start using macros to automate formating and sorting with macros back then. If I could have remembered what the key combo was I could have probably nailed it down, but that was 7 years ago.

later

Ty