Highlight selected row

[gailb]

I have this code which works fine for highlighting the row the user selected. Is it possible to make this work in an add-in instead of using the worksheet code. That way, any worksheet that opens will be able to utilize the Highlight row. Kind of like the Highlight filter add-in Rory posted.

Code: Select all

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim LastRow As Long: LastRow = Me.Range("A" & Rows.Count).End(xlUp).Row
    Dim selRow  As Long: selRow = Target.Row
    If selRow > LastRow Then Exit Sub
    If selRow = 1 Then Exit Sub
    Dim LastCol As Long: LastCol = Me.Cells(1, Columns.Count).End(xlToLeft).Column
    Cells.Interior.ColorIndex = xlNone
    Range(Cells(selRow, 1), Cells(selRow, LastCol)).Interior.ColorIndex = 37
End Sub

[HansV]

Here you go. Don't forget to install the add-in through File > Options > Add-ins.

Highlight.zip

[gailb]

Wow, thanks Hans. It never even crossed my mind to use the workbook event.

[HansV]

I used a special construct: a WithEvents object. This allows us to define an application-wide event handler.