Highlight active row/column in Excel without using VBA?

Question

What I want to achieve is to highlight active row or column. I used VBA solutions but everytime Selection_change event is used I am loosing chance to undo any changes in my worksheet.

Is there a way to somehow highlight active row / column without using VBA?

Answer 1

I don't think it can be done without using VBA, but it can be done without losing your undo history:

In VBA, add the following to your worksheet object:

Public SelectedRow as Integer
Public SelectedCol as Integer

Private Sub Worksheet_SelectionChange(ByVal Target as Range)
    SelectedRow = Target.Row
    SelectedCol = Target.Column
    Application.CalculateFull ''// this forces all formulas to update
End Sub

Create a new VBA module and add the following:

Public function HighlightSelection(ByVal Target as Range) as Boolean
    HighlightSelection = (Target.Row = Sheet1.SelectedRow) Or _
        (Target.Column = Sheet1.SelectedCol)
End Function

Finally, use conditional formatting to highlight cells based on the 'HighlightSelection' formula: