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. Press with right mouse button on 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

Get the Excel file

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

Macro category

Save invoice data [VBA]
This article demonstrates a macro that copies values between sheets. I am using the invoice template workbook. This macro copies […]

Open Excel files in a folder [VBA]
This tutorial shows you how to list excel files in a specific folder and create adjacent checkboxes, using VBA. The […]

Split data across multiple sheets [VBA]
In this post I am going to show how to create a new sheet for each airplane using vba. The […]

With end with statement category

Extract cell references from a formula
This article demonstrates a User Defined Function that allows you to extract cell references from a formula in a given […]

Customize Excel maze
Terry wants to make a different sized maze and I think that is a great idea. Perhaps you remember that I […]

How to use the WITH … END WITH statement
The With ... End With statement allows you to write shorter code by referring to an object only once instead […]

Excel categories

Comments (13)