Highlighting a Row

March 11th, 2007

Suppose you have an Excel table like the one below and whenever the user clicks on any cell in the table, you wanted to highlight the row.

The code below will highlight a row whenever a cell inside the table is clicked.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Static lPreviousRow As Long
On Error Resume Next
If Target.Rows.Count> 1 Then Exit Sub
If Target.Row <3 Or Target.Row> 11 Or Target.Column <2 Or Target.Column> 7 Then Exit Sub

If Target.Row <> lPreviousRow Then
With Application.Intersect(Me.Range("B:G"), Me.Range(lPreviousRow & ":" & lPreviousRow))
.Interior.ColorIndex = xlColorIndexNone
lPreviousRow = Target.Row
End With
With Application.Intersect(Me.Range("B:G"), Me.Range(lPreviousRow & ":" & lPreviousRow))
.Interior.ColorIndex = 39
End With
End If
End Sub

You can download the workbook that demonstrates this here .

Posted in Excel, VBA | No Comments

No Comments yet »

RSS feed for comments on this post. TrackBack URI

Vergel’s Excel and Visual Basic Blog