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.

 

[marknhopgood]

Unlike other methods this doesn't interfere with cell contents.
What I do is draw 2 rectangles on the sheet and make them transparent. These will act as my cross hairs.
Then in the active sheet I add the following code in VBA...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)


With ActiveSheet.Shapes("Rectangle 1")
.Left = 0
.Top = Target.Top
.Height = Target.Height
.Width = 19638
End With


With ActiveSheet.Shapes("Rectangle 2")
.Left = Target.Left
.Top = 0
.Height = 19638
.Width = Target.Width
End With


End Sub

 

[Chrisdudley7]

Ok I also tried putting this macro in but I get this 'Argument not optional' message? Do the rectangles need to be the exact size of the ones in your example for this macro to work? I just draw the rectangles in my spreadsheet or do I actually highlight the column and the row that are to intersect?

 

[marknhopgood]

Drawing 2 rectangles should do it. They do not need to be exact size as the code resizes automatically.

Check the names of the rectangles.
Sometimes Excel names them Rectangle 3 and Rectangle 4 - depends on whether there have been previous objects in the sheet.
Change the code to reflect the exact rectangle names

in

With ActiveSheet.Shapes("Rectangle 1")]#

hope this helps.
regards
Mark

 

[Chrisdudley7]

When I run the macro, it is not available for selection. Also, how do I check the names of the rectangles?

Do I need to add that ]# to the end of the line?

 

[Tyron]

Hey Guys,

That is funny. I just got request to find out how to do this again. I remember in excel 2003 there used to be a key combination to activate this. Anybody remember how this was done. I used this all the time at a previous job. Didn't require any VBA either.

later

Ty

 

[look]

Hi JamesFrames8,

Found this somewhere:

[TABLE="width: 42"]
<colgroup><col width="56" style="width: 42pt;"> <tbody>[TR]
[TD="width: 56, bgcolor: transparent"][TABLE="width: 42"]
<colgroup><col width="56" style="width: 42pt;"> <tbody>[TR]
[TD="width: 56, bgcolor: transparent"]Sub Worksheet_SelectionChange(ByVal Target As Range)[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Cells.Interior.ColorIndex = xlNone[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]ActiveCell.EntireRow.Interior.ColorIndex = 34[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]ActiveCell.EntireColumn.Interior.ColorIndex = 34[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]End Sub[/TD]
[/TR]
</tbody>[/TABLE]

Will highlight the cell. Can change the color of highlightby changing number ie 34.

Thanks
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 42"]
<colgroup><col width="56" style="width: 42pt;"> <tbody>[TR]
[TD="width: 56, bgcolor: transparent"]
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

 

[Worf]

The issue with the above code is that it will erase any color formatting previously applied. The following implements Control+h as the shortcut for it:

' at sheet module
Private Sub Worksheet_Activate()
Application.OnKey "^h", "CrossHair"
End Sub


' at a module
Sub CrossHair()
Union(ActiveCell.EntireColumn, ActiveCell.EntireRow).Select
End Sub

 

[Tyron]

Hey Guys,

Does anyone remember what version of Excel it was that actually had it built in?

later

Ty

 

[mikerickson]

Here's another way.

Make these UDF's

Function ActiveRow() As Long
    ActiveRow = ActiveCell.Row
End Function
Function ActiveColumn() As Long
    ActiveColumn = ActiveCell.Column
End Function

then set conditional formatting on all cells with the formula
=OR(ROW(A1)=ActiveRow(), COLUMN(A1)=ActiveColumn())

Then put this in the sheet's code module.

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