CODEWITHSUNDEEP
excelvba
twfurst
twfurst

Reputation: 263

Excel VBA Row Highlighting

I've found some examples of how to highlight the row of the currently selected cell. My issue is that I need to do this only in rows 3 and higher.

This is what I picked up so far:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
    Application.ScreenUpdating = False
    Cells.Interior.ColorIndex = 0
    With Target
        .EntireRow.Interior.ColorIndex = 8
    End With
    Application.ScreenUpdating = True
End Sub

This works as advertised, but I am struggling with how to not lose the background color I have for header cells in rows 1 and 2. I am sure it requires an "if" of some sort, but I am not sure where I need to put it.

Also, is there anyway I can apply this to the entire workbook? I have 60 sheets in the workbook and if I can not replicate the code 60 times that would be ideal.

Any help is greatly appreciated.

Upvotes: 0

Views: 178

Answers (1)

DragonSamu
DragonSamu

Reputation: 1163

The following code will do the trick:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
        If Target.Row <> 1 and Target.Row <> 2 Then
            Application.ScreenUpdating = False
            Cells.Interior.ColorIndex = 0
            Range("A1:AF2").Interior.ColorIndex = 47
            Target.EntireRow.Interior.ColorIndex = 8
            Application.ScreenUpdating = True
        End If
End Sub

you place this in ThisWorkbook instead of the specific Sheet.

the code:

If Target.Row <> 1 and Target.Row <> 2 Then

Checks if Target.Row does not equal Row 1 and 2

Upvotes: 1

Related Questions