Today I would like to share to you these small event handler procedures that makes it easier for you to read data on the same row or column. These procedures highlight a row and column of the selected cell. Make sure you don´t have colored cells in your worksheet as the first four procedures remove colors from cells.

The last procedure is different, it creates borders on the same row as the selected cell. You can use this procedure if you have colored cells on your sheet.

Highlight row

This event code highlights the entire row of the selected cell. I have chosen the color green.

Highlight row5

Instructions
Right click on sheet and select "View Code". Copy the code below and paste to the module.

VBA Code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Cells.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
End With
With Rows(Selection.Row).Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 5296274
        .TintAndShade = 0
        .PatternTintAndShade = 0
End With
End Sub

Highlight column

This Event code highlights entire column of the selected cell.

highlight column

Instructions
Right click on sheet and select "View Code". Copy the code below and paste to the module.

The only difference between this event code and the one above is this line:
With Columns(Selection.Column).Interior

VBA Code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Cells.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
End With
With Columns(Selection.Column).Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 5296274
        .TintAndShade = 0
        .PatternTintAndShade = 0
End With
End Sub

Highlight row and column

This event code highlights the entire column and row of the selected cell.

highlight row and column

Instructions
Right click on sheet and select "View Code". Copy the code below and paste to the module.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Cells.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
End With
With Rows(Selection.Row).Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 5296274
        .TintAndShade = 0
        .PatternTintAndShade = 0
End With
With Columns(Selection.Column).Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 5296274
        .TintAndShade = 0
        .PatternTintAndShade = 0
End With
End Sub

Highlight multiple cells

This event code allows you to highlight entire columns and rows of multiple selected cells. Press and hold SHIFT key to select multiple cells with your mouse.

highlight multiple cells

Instructions
Right click on sheet and select "View Code". Copy the code below and paste to the module.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Cells.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
End With
For Each cell In Selection
    With Rows(cell.Row).Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 5296274
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    With Columns(cell.Column).Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 5296274
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    With cell.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 5287936
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
Next cell
End Sub

Apply borders to row of the selected cell

This event procedure applies borders to the row. It does not remove colors from cells instead it removes all borders every time you select a new cell.

apply borders

Instructions
Right click on sheet and select "View Code". Copy the code below and paste to the module.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Cells
    .Borders(xlDiagonalDown).LineStyle = xlNone
    .Borders(xlDiagonalUp).LineStyle = xlNone
    .Borders(xlEdgeLeft).LineStyle = xlNone
    .Borders(xlEdgeTop).LineStyle = xlNone
    .Borders(xlEdgeBottom).LineStyle = xlNone
    .Borders(xlEdgeRight).LineStyle = xlNone
    .Borders(xlInsideVertical).LineStyle = xlNone
    .Borders(xlInsideHorizontal).LineStyle = xlNone
End With
With Rows(Selection.Row).Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
End With
With Rows(Selection.Row).Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThin
End With
End Sub

Download excel *.xlsm file

Highlight selected cells row and columns.xlsm