Active Row Conditional Formatting

glerwell

Well-known Member
Joined
Jun 25, 2006
Messages
1,082
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
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Hi glerwell

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

Code:
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.
 
Upvote 0

Forum statistics

Threads
1,223,937
Messages
6,175,512
Members
452,650
Latest member
Tinfish

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top