Highlighting the Rows of Selected Cells

@Mike H

I believe that Microsoft365 has only had this feature for about 6 months, and needs an annual subscription.

This tip has made it available to non-subscription users for about 6 years - for nothing! It works fine in Excel2010, and probably earlier.

I believe this is no longer necessary with the recent advent of View Tab - Focus Cell

In his comment of 7 June 2018 Samuel is right, as well this tip's macro as my version in the previous post make the undo stack non functional. It is possible though to accomplish the highlighting with conditional formatting instead of by using these macros. Here is how:
- Select the range for which you want to apply the formatting (e.g. the whole sheet).
- Click "Conditional Formatting" in the "Styles" section of the ribbon's "Home" tab.
- Select "New Rule" and then choose "Use a formula to determine which cells to format".
- In the box "Format values where this formula is true:" enter the following formula:
=CELL("row")=ROW() for highlighting only the entire row
=OR(CELL("row")=ROW(),CELL("col")=COLUMN()) for both row and column.
- Click the "Format" button and select the "Fill" tab.
- Select the color you want and click OK three times.

This conditional formatting does however require a recalc in order to function properly. This can be done by this event macro:

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

The Target.Calculate statement doesn't affect the undo stack which remains functional.

Here is a simplified version of this tip's macro:

Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Static X As Long
Static Y As Long
If X <> 0 Then
Columns(X).Interior.ColorIndex = xlNone
Rows(Y).Interior.ColorIndex = xlNone
End If
X = Target.Column
Columns(X).Interior.ColorIndex = 20
Y = Target.Row
Rows(Y).Interior.ColorIndex = 20
End Sub

And if you only want the row to be highlighted (as I do), this little macro will do the job:

Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Static Y As Long
If Y <> 0 Then Rows(Y).Interior.ColorIndex = xlNone
Y = Target.Row
Rows(Y).Interior.ColorIndex = 20
End Sub

Thanks great and simple tool. although i noticed that it doesn't allow undo (Ctrl+Z) and auto save. perhaps you could look into it

Thank you great

I like that there is no conditional formatting associated with this code.

How can I modify the VBA to highlight the row to the left and the column up?

Is there a way to keep the VBA from overwriting any other non conditional color formatting I am using?

Thanks, Mark

Personnaly I increase the heigth of that row.