Highlight Current Row and Column in Excel Using VBA

6/27/2014 8:40:24 PM

The following VBA Scipt will highlight the current selected row in Excel. This is helpful when you are reading data horizontally and you want to make sure you are reading the correct columns all the way across. The commented section will allow you to only highlight certain columns.

To set the script, hit ALT-F11, select your current workbook and worksheet on the left, double-click the worksheet, plaste code and hit save.

Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

Static xRow
Static xColumn

'If only highlight column 1 & 2, unhighlight column1 & 2
'Columns(1).Interior.ColorIndex = xlNone
'Columns(2).Interior.ColorIndex = xlNone

'Unhighlight columns and rows
If xColumn <> "" Then
With Columns(xColumn).Interior
.ColorIndex = xlNone
End With
With Rows(xRow).Interior
.ColorIndex = xlNone
End With
End If

pRow = Selection.Row
pColumn = Selection.Column
xRow = pRow
xColumn = pColumn

'uncomment to also highlight the column
'With Columns(pColumn).Interior
'.ColorIndex = 6
'.Pattern = xlSolid
'End With

'MsgBox pRow

'highlight column 1 & 2
'Range("A" & pRow).Interior.ColorIndex = 6
'Range("A" & pRow).Interior.Pattern = xlSolid
'Range("B" & pRow).Interior.ColorIndex = 6
'Range("B" & pRow).Interior.Pattern = xlSolid

With Rows(pRow).Interior
.ColorIndex = 6
.Pattern = xlSolid
End With

End Sub