Feedback by UserVoice

How can we improve Excel for Windows (Desktop Application)?

Crosshair display

A simple display option to easily navigate around larger spreadsheets.

The entire row & column of the cell selected would be highlighted in some way.

I've used the following macro to achieve this:

Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
'Update 20140318
Static xRow
Static xColumn
If xColumn <> "" Then
With Columns(xColumn).Interior
.ColorIndex = xlNone
End With
With Rows(xRow).Interior
.ColorIndex = xlNone
End With
End If
pRow = Selection.Row
pColumn = Selection.Column
xRow = pRow
xColumn = pColumn
With Columns(pColumn).Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
With Rows(pRow).Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
End Sub

The only problem I have with this is it cannot be used in a worksheet with cell formatting.

5 votes
Sign in
(thinking…)
Sign in with: Facebook Google
Signed in as (Sign out)

We’ll send you updates on this idea

Daniel Wilson shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

2 comments

Sign in
(thinking…)
Sign in with: Facebook Google
Signed in as (Sign out)
Submitting...
  • Anonymous commented  ·   ·  Flag as inappropriate

    @KERTAL
    I've added \

    If (lastrow <> Empty) And (lastcol <> Empty) Then

    after On Error GoTo 0

    and End If before End Sub
    because it was crashing on empty sheet

  • KERATL commented  ·   ·  Flag as inappropriate

    Daniel, try this; as long as you don't click precisely on the lines (which selects them), it works ok and doesn't interfere with cell formatting. I think it would be annoying to have it active all the time, if I were going to use this I'd probably add a button on the ribbon (connected to a VBA snippet) to turn the functionality on/off.

    Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

    'identify the used range for the active worksheet
    'no sense in making the line indicator wider/taller than necessary
    On Error Resume Next
    'get the last row
    LastRow = ActiveSheet.Cells.Find(What:="*", After:=ActiveSheet.Range("A1"), _
    Lookat:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, _
    SearchDirection:=xlPrevious, MatchCase:=False).Row
    'get the last column
    LastCol = ActiveSheet.Cells.Find(What:="*", After:=ActiveSheet.Range("A1"), _
    Lookat:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByColumns, _
    SearchDirection:=xlPrevious, MatchCase:=False).Column
    'remove any previous indicator lines (must remain within the error checking zone)
    ActiveSheet.Shapes("tHorizontal").Delete
    ActiveSheet.Shapes("tVertical").Delete
    On Error GoTo 0

    'Now we need to find the last cell in the used range
    Dim Lastcell As Range
    Set Lastcell = ActiveSheet.Range("A1").Offset(LastRow - 1, LastCol - 1) ', 1, 1)
    'and it's location to get max indicator size(s), include cell size for better optics
    LastCellLeft = Lastcell.Left + Lastcell.Width
    LastCellTop = Lastcell.Top + Lastcell.Height

    'target = selection
    tLeft = Target.Left
    tTop = Target.Top
    tWidth = Target.Width
    tHeight = Target.Height
    'This tells us where to position the two indicators
    'x coordinate center
    tCenterHorizontal = tLeft + (tWidth * 0.5)
    'y coordinate center
    tCenterVertical = tTop + (tHeight * 0.5)

    TargetLineWidth = 1
    TargetOffset = 5 'offset from row/column IDs
    'if the clicked cell is outside the used range, use that for better optics
    LastCellLeft = Application.Max(LastCellLeft, (tLeft + tWidth))
    LastCellTop = Application.Max(LastCellTop, (tTop + tHeight))

    Dim tShape1 As Object
    Dim tShape2 As Object

    Set tShape1 = ActiveSheet.Shapes.AddShape(msoShapeRectangle, TargetOffset, tCenterVertical, LastCellLeft, TargetLineWidth)
    With tShape1
    .Name = "tHorizontal"
    .Line.Visible = msoFalse
    .Visible = msoTrue
    .Fill.ForeColor.RGB = RGB(255, 200, 150)
    .Fill.Transparency = 0.5
    .Fill.Solid
    End With

    Set tShape2 = ActiveSheet.Shapes.AddShape(msoShapeRectangle, tCenterHorizontal, TargetOffset, TargetLineWidth, LastCellTop)
    With tShape2
    .Name = "tVertical"
    .Line.Visible = msoFalse
    .Visible = msoTrue
    .Fill.ForeColor.RGB = RGB(255, 200, 150)
    .Fill.Transparency = 0.5
    .Fill.Solid
    End With

    End Sub

Feedback and Knowledge Base