Active Row Conditional Formatting

[glerwell]

Hi guys

Im using a formula with conditional formatting and a change event code to highlight a selection of a row when a cell within that row is selected.

Everything works fine, however can I get this to only highlight the selection when the active cell is within a range.

The range I want is L10:P45, so if the active cell is within that range, the formating kicks in but if it isn't in that range then no formatting occurs.

Could I also have the formating to be applied if I only hover over a cell instead of clicking on the cell?

This is my formula for the conditional formatting:

=CELL("row")=ROW()

The event code:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Application.ScreenUpdating = True
End Sub

Thanks

 

[pgc01]

Hi glerwell

You can use a name to store the active row when the active cell is in L10:P45

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 
If Not Intersect(Range("L10:P45"), Target) Is Nothing Then
    ActiveWorkbook.Names.Add Name:="ActRow", RefersToR1C1:="=" & Target.Row
Else
    ActiveWorkbook.Names.Add Name:="ActRow", RefersToR1C1:=" "
End If
End Sub

Now select L10:P45 and enter for the formula of the conditional formatting:

=ROW()=ActRow

Now when you select a cell in L10:P45 the conditional formatting will kick in for the cells in that row in columns L:P.

 

[glerwell]

hi pgc01

that works great, thanks for the modification of the code and formula!!