VBA Highlighting the selected row leads to selection of the entire row

Question

I made up a code that highlights the selected row. If selection changes - the newly selected row is highlighted and the previous selection's formatting returns to initial one. I use

row 9 as highlight formatting sample and
row 10 as baseline for conditional formatting of unselected rows.

The code works fine. However, when the cell is selected, the row gets highlighted, the selected cell remains active BUT the whole row is selected. Can someone please help me to deselect everything except the target cell?

Nothing from here helps.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

LastRowA = Range("A" & Rows.Count).End(xlUp).Row

If Target.Cells.Count > 1 Or Target.Cells.Count < 1 Then 'If selected 1 cell
    'Do nothing
Else
    Application.ScreenUpdating = False
    If Target.Row > 10 And Target.Row < LastRowA + 1 Then

        Rows("10:10").Copy 'Restore all rows to custom conditional formatting of row 10
        For tableRow = 11 To LastRowA 
            Rows(tableRow & ":" & tableRow).PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        Next tableRow

        Rows("9:9").Copy 'Highlight active row using formating of row #9
        Rows(Target.Row).PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

        Application.CutCopyMode = False
        Target.Cells.Activate 'Return Target to initially selected cell
    End If
    Application.ScreenUpdating = True
End If

End Sub

Try changing your `Rows(Target.Row).PasteSpecial` to `Cells(Target.Row,Target.Column).PasteSpecial` — BruceWayne, Dec 21 '15 at 19:47
@BruceWayne - in that case, then only one cell would get the formatting pasted. — Scott Holtzman, Dec 21 '15 at 20:15

user1582568 · Accepted Answer · 2015-12-21T20:03:55.227

Try this

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim LastRowA As Long
Dim tableRow As Long

LastRowA = Range("A" & Rows.Count).End(xlUp).Row

If Target.Cells.Count > 1 Or Target.Cells.Count < 1 Then 'If selected 1 cell
    'Do nothing
Else
    Application.ScreenUpdating = False
    If Target.Row > 10 And Target.Row < LastRowA + 1 Then

        Rows("10:10").Copy 'Restore all rows to custom conditional formatting of row 10
        For tableRow = 11 To LastRowA
            Rows(tableRow & ":" & tableRow).PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        Next tableRow

        Rows("9:9").Copy 'Highlight active row using formating of row #9
        Rows(Target.Row).PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

        Application.CutCopyMode = False
        Target.Cells.Activate 'Return Target to initially selected cell
        Application.EnableEvents = False
        Target.Cells.Select
        Application.EnableEvents = True
    End If
    Application.ScreenUpdating = True
End If

End Sub

Nice with the `EnableEvents = False`. I would add that the Loop can be replaced with just `Range(Cells(11,1),Cell(LastRowA,Columns.Count)).PasteSpecial ...` — Scott Holtzman, Dec 21 '15 at 20:19

VBA Highlighting the selected row leads to selection of the entire row

1 Answers1