Highlight row and column of selected cell
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
Extract cell references from a formula
I am trying to build a regular expression that matches cell references in a formula. A regular expression is a […]
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 […]
13 Responses to “Highlight row and column of selected cell”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Great job and is very useful
Thank you very much Mr. (Oscar)
Thank you!
Work more than wonderful, because you are a wonderful person
Dear Oscar
i want to highlight the row if checkbox in my customised menu is clicked.
please help & provide code.
Dear Oscar
In your first example Excel highlights all rows until last column (XFD). I need highlight from column A to column R. Is it possible?
Thanks!
Hi Oscar,
Thanks a lot for all these wonderful macros.
I wanna know how the codes will be if I want to maintain the headers with blue color and the color changes happens only to the cells below the header.
hello
i want program with vba
with click a picture count into cell
thanks
Hi Oscar,
Is it possible to highlight a row but only within a table?
Thanks
Nilhan
Hi Oscar,
First i want to tell you Big Thank You for these examples and working codes.
I would like to as, is there a way to maintain all other colors on my sheet and still get benefit from "Highlight row and column"/code/?
If you can just send me this tipe of code on my email i will be very thankful.
BR,
Martin
Dear Sir.
Big thanks for examples and working codes is very useful
please explan how to change colour
Sanjaya
Sanjaya,
I believe you change this:
Interior.Color property (Excel)
Thanks a lot for your sharing.
How can we use this as default in excel?
Zin Thida Win
You can save it to your personal workbook and always have access to it.
Copy your macros to a Personal Macro Workbook
Add your personal Excel Macros to the ribbon