get-digital-help.com

Today I would like to share with you these small event handler procedures that make 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.

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.

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.

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.

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.

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