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.

1. How to highlight a row automatically using event macro?

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

How to add code to your workbook

1. Right-click on the worksheet name and select "View Code". This opens the Visual Basic Editor and the corresponding module.
   
2. Copy the VBA code below and paste it to the module.
   
3. Exit the VB Editor and go back to Excel.

VBA Code

'Eventcode procedure name, you can't change this line
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'The With ... End With statement allows you to write shorter code by referring to an object only once instead of using it with each property.
'These lines removes cell colors
With Cells.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
End With

'These lines applies cell colors to all cells in row of selected cell
With Rows(Selection.Row).Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 5296274
        .TintAndShade = 0
        .PatternTintAndShade = 0
End With
End Sub

Microsoft Docs: Cells | Interior | Pattern | TintAndShade | PatternTintAndShade | PatternColorIndex | Color | Rows

Back to top

2. Highlight column of the selected cell

This Event code highlights the entire column of the selected cell.

How to add code to your workbook

VBA Code

'Event code
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'The With ... End With statement allows you to write shorter code by referring to an object only once instead of using it with each property. 
'These lines removes cell colors
With Cells.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
End With

'These lines applies cell colors to all cells in column of selected cell
With Columns(Selection.Column).Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 5296274
        .TintAndShade = 0
        .PatternTintAndShade = 0
End With

End Sub

Microsoft Docs: Cells | Interior | Pattern | TintAndShade | PatternTintAndShade | PatternColorIndex | Color | Columns | Selection | Selection.Column

Back to top

3. Highlight row and column of the selected cell

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

How to add code to your workbook

VBA code

'Event code
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'The With ... End With statement allows you to write shorter code by referring to an object only once instead of using it with each property. 
'These lines removes cell colors
With Cells.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
End With

'These lines applies cell colors to all cells in row of selected cell
With Rows(Selection.Row).Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 5296274
        .TintAndShade = 0
        .PatternTintAndShade = 0
End With

'These lines applies cell colors to all cells in column of selected cell
With Columns(Selection.Column).Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 5296274
        .TintAndShade = 0
        .PatternTintAndShade = 0
End With

End Sub

Microsoft Docs: Cells | Interior | Pattern | TintAndShade | PatternTintAndShade | PatternColorIndex | Color | Columns | Selection | Selection.Column |  Rows | Selection.Row

Back to top

4. Highlight rows and columns of multiple selected cells

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

How to add code to your workbook

'Event code
Private Sub Worksheet_SelectionChange(ByVal Target As Range)


'The With ... End With statement allows you to write shorter code by referring to an object only once instead of using it with each property. 
'These lines removes cell colors
With Cells.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
End With

'Iterate through all cells in selection
For Each cell In Selection
    
    'The With ... End With statement allows you to write shorter code by referring to an object only once instead of using it with each property. 
    'These lines removes cell colors
    With Rows(cell.Row).Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 5296274
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    
    'These lines applies cell colors to all cells in column of selected cell
    With Columns(cell.Column).Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 5296274
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    
    'These lines applies cell colors to all cells in row of selected cell
    With cell.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 5287936
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    
Next cell

End Sub

Microsoft Docs: Cells | Interior | Pattern | TintAndShade | PatternTintAndShade | PatternColorIndex | Color | Columns | Selection | Rows

5. Apply borders to the 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.

How to add code to your workbook

'Event code
Private Sub Worksheet_SelectionChange(ByVal Target As Range)


'The With ... End With statement allows you to write shorter code by referring to an object only once instead of using it with each property. 
'These lines removes cell colors
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

'These lines enables a top cell border to all cells in row of selected cell
With Rows(Selection.Row).Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
End With

'These lines enables a bottom cell border to all cells in row of selected cell
With Rows(Selection.Row).Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThin
End With

End Sub

Microsoft Docs: Cells | Interior | Pattern | TintAndShade | PatternTintAndShade | PatternColorIndex | Color | Columns | Selection | Rows | Cells.Borders | LineStyle

Back to top

Download Excel file

Highlight-selected-cells-row-and-columns.xlsm

---

Weekly Blog EMAIL

Email

Welcome! I am Oscar and here to help you out.

Feel free to comment and ask Excel questions.

Make sure you subscribe to my newsletter so you don't miss new blog articles.

Related articles

---