Use Conditional Format to automatically highlight active Rows on UsedRange

Question

I need to automatically highlight active Rows using Conditional Format.
The below code works successfully with only one active row.
I mean by active rows, the cells which manually selected by using mouse or hold Ctrl button.

Sub Highlight_active_Rows()

    Dim ws As Worksheet:    Set ws = ActiveSheet
    Dim rng As Range
    
    Set rng = ws.UsedRange.Resize(ws.UsedRange.Rows.count - 2).Offset(2)    '(UsedRange except Two Row)
    
    With rng
      .FormatConditions.Add Type:=xlExpression, Formula1:="=ROW()=CELL(""Row"")"
      .FormatConditions(rng.FormatConditions.count).SetFirstPriority
      .FormatConditions(1).Interior.Color = RGB(250, 190, 142)
      .FormatConditions(1).StopIfTrue = False
    End With 
End Sub

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

Answer 1

• CELL("Row") return the row index of the first cell in selection, so it can not highlight multiple rows

Consider taking a different approach.

• The event code involves conditional formatting in the used range.
• It's possible that there is pre-existing conditional formatting in your file. The new FC temporarily overwrites the original one but reverts after a selection change.

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim rRow As Range, useRng As Range, oFC As FormatCondition
    ' Get the target range
    Set useRng = Me.UsedRange.Resize(Me.UsedRange.Rows.Count - 2).Offset(2)
    ' Remove the FCs which are created by SelectionChange events
    For Each oFC In useRng.FormatConditions
        If oFC.Formula1 = "=TRUE" Then oFC.Delete
    Next
    ' Get the intersection range
    Set rRow = Application.Intersect(Target.EntireRow, useRng)
    If Not rRow Is Nothing Then
        ' Add new FC
        Set oFC = rRow.FormatConditions.Add(Type:=xlExpression, Formula1:="True")
        With oFC
            .SetFirstPriority
            .Interior.Color = RGB(250, 190, 142)
            .StopIfTrue = True
        End With
    End If
End Sub