Hightlight active row/column in Excel without usin

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 hightlight active row / column without using VBA?

Answer 1

On the sheets Selection_change event call the following:

Function highlight_Row(rngTarget As Range)
    Dim strRangeRow As String
    strRangeRow = rngTarget.Row
    strRangeRow = strRangeRow & ":" & strRangeRow
    Rows(strRangeRow).Select
    rngTarget.Activate
End Function

This is long format for clarity!

Answer 2

The best you can get is using conditional Formatting.

Create two formula based rules:

1. =ROW()=CELL("row")
2. =COLUMN()=CELL("col")

As shown in:

The only drawback is that every time you select a cell you need to recalculate your sheet. (You can press "F9")

Answer 3

First of all Thanks! I had just created a solution with highlighting cells, using the Selection_Change and changing a cells content. I did not know it would disable Undo. I found a way to do it by using combining conditional formatting, Cell() and the Selection_Change event. This is how I did it.

• In Cell A1 I put the formula =Cell("row")
• Row 2 is completely empty
• Row 3 contains the headers
• Row 4 and down is the data
• To make the formula in A1 to be updated, the sheet need to recalculate. I can do that with F9, but I created the Selection_Change event with the only code to be executed is Range("A1").Calculate. This way it is done every time the user moves around, and as the Selection_Change is NOT changing any values/formats etc in the sheet, Undo is not disabled.
• Now just enter the conditional formatting to highlight the cells that have the same row as cell A1.
  • Select the whole column B
  • Conditional Formatting, Manage Rules, New Rule, Use a Formula to determine which cells to format
  • Enter this formula: =Row(B1)=$A$1
  • Click Format and select how you want it to be highlighted
  • Ready. Press OK in the popups.

This works for me.

Answer 4

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:

Answer 5

Using conditional formatting, instead of highlighting the entire row and column, it is possible to highlight the row to the left of the cell and the column above the cell with the code below:

=OR(AND(CELL("col")=COLUMN();(CELL("row")-1)>=ROW());AND(CELL("col")>=COLUMN();(CELL("row")-1)=ROW()))

Answer 6

An alternative to Range.Calculate is using ActiveWindow.SmallScroll The only downside is that the screen flickers for a split second after making a new selection. While scrolling manually, you need to make sure the new selection moves out of the screen (window) completely, for it to work. Which is why, in below code, we need to scroll enough to get all visible rows out of the screen view and then scroll back to same position -to force screen refresh for conditional formatting.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ScreenUpdating = False
ActiveWindow.SmallScroll Down:=150 'change these values to total rows displayed on screen
ActiveWindow.SmallScroll Down:=-150 'change these values to total rows displayed on screen
'DoEvents 'unable to use this to remove the screen flicker
ScreenUpdating = True
End Sub

Credits: Rory Archibald https://www.experts-exchange.com/questions/28275889/When-is-excel-conditional-formatting-refreshed.html