Note: The other languages of the website are Google-translated. Back to English
Log in Register

How to auto-highlight row and column of active cell in Excel?

When you view a large worksheet with numerous data, you may want to highlight the selected cell’ row and column so that you can easily and intuitively read the data to avoid misreading them. Here, I can introduce you some interesting tricks to highlight the row and column of the current cell, when the cell is changed, the column and row of the new cell are highlighted automatically as following screenshots:

Auto-highlight row and column of selected cell with VBA code
One click to highlight row and column of selected cell

Auto-highlight row and column of selected cell with VBA code

The following VBA code can help you automatically highlight the entire column and row of the current cell in worksheet, please do with following steps:

1. Open the worksheet you will auto-highlight the row and column of active cell, right click the sheet tab and select View Code from the context menu.

2. Then copy and paste following VBA code into the blank code window:

VBA code: auto-highlight row and column of selected cell

Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
'Update 20200430
Static xRow
Static xColumn
If xColumn <> "" Then
    With Columns(xColumn).Interior
        .ColorIndex = xlNone
    End With
    With Rows(xRow).Interior
        .ColorIndex = xlNone
    End With
End If
pRow = Selection.Row
pColumn = Selection.Column
xRow = pRow
xColumn = pColumn
With Columns(pColumn).Interior
    .ColorIndex = 6
    .Pattern = xlSolid
End With
With Rows(pRow).Interior
    .ColorIndex = 6
    .Pattern = xlSolid
End With
End Sub

3. Then press the Alt + Q keys together to return to the worksheet, now when you select a cell, the entire row and column of this cell has been highlighted. And it will be dynamically moved as the selected cell changes.

doc-reading-layout4

Notes:

  • 1. In the above code, you can change .ColorIndex = 6 color to other color you like.
  • 2. This VBA code only apply to the worksheet you choose in the above step 2.
  • 3. If you do not want to highlight the column and row of the selected cell again, you need to delete the above VBA and set no fill to the selected row and column by clicking Home > Fill color > No fill.
  • 4. If there are some colored cells in your worksheet, the color will be lost when you click the cell and then move to other cell.

Auto-highlight row and column of selected cell with Kutools for Excel

If you are a newbie of VBA, here recommend the Kutools for Excel's Reading Layout utility. This utility helps to easily highlight row and column of selected cell in Excel as the below demo shown. Download and try it now! ( 30-day free trail)

Let's see how to apply the Kutools for Excel’s Reading Layout feature to highlight entire rows and columns of a range of cells.

1. Click Kutools > Reading Layout. See screenshot:

Then the reading layout is enabled, you can see the row and column of active cell are highlighted immediately. And it will be dynamically moved as the selected cells change.

Notes:

  • 1. You can change the reading layout settings based on your needs as below screenshot shown.
  • 2. You can cancel this utility by unchecking the Reading Layout under Reading drop down list.
  • 3. This Reading Layout View feature is applied to all of the worksheets within your workbook.
  • 4. This Reading Layout View feature will disabled when you launch the workbook next time.

  If you want to have a free trial ( 30-day) of this utility, please click to download it, and then go to apply the operation according above steps.

One click to highlight row and column of selected cell

The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
Comments (50)
No ratings yet. Be the first to rate!
Suresh Singh
Suresh Singh
This comment was minimized by the moderator on the site
Hi, the given code works fine for highlighting the row and coloumn of active cell. but the original cell colour along with coloumn and rows colour settings are lost. How to overcome this?
Reply
RaveFromDave
RaveFromDave     Suresh Singh
This comment was minimized by the moderator on the site
Here's my version for highlighting the current row (from A to AK). To avoid wiping out cell colouring I use a red border instead...plus it restores my preferred border to the row just left. Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim lRow As Long, lCol As Long Static lPrevRow As Long 'Restore previous active row to...light blue dotted line If lPrevRow > 0 Then Range("A" & lPrevRow, "AK" & lPrevRow).BorderAround Weight:=xlHairline, Color:=RGB(162, 200, 255) End If 'Highlight current row with a red border lRow = Target.Row Range("A" & lRow, "AK" & lRow).BorderAround Weight:=xlThin, Color:=RGB(255, 0, 0) lPrevRow = lRow End Sub
Reply
farfan
farfan     RaveFromDave
This comment was minimized by the moderator on the site
Oh! man, works like a charm!!! great
Reply
calixco
calixco     RaveFromDave
This comment was minimized by the moderator on the site
you are a geniuos now, how abot rows and columsn?
Reply
MARIO
MARIO     RaveFromDave
This comment was minimized by the moderator on the site
HI I HAVE THE SAME PROBLEM WITH SURESH CAN YOU PLEASE PROVIDE ME WITH THE WHOLE AMENDED CODE FOR NOT WIPING THE ORIGINAL CELL COLOUR I'M USING THIS: Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) 'Update 20140318 Static xRow Static xColumn If xColumn "" Then With Columns(xColumn).Interior .ColorIndex = xlNone End With With Rows(xRow).Interior .ColorIndex = xlNone End With End If pRow = Selection.Row pColumn = Selection.Column xRow = pRow xColumn = pColumn With Columns(pColumn).Interior .ColorIndex = 6 .Pattern = xlSolid End With With Rows(pRow).Interior .ColorIndex = 6 .Pattern = xlSolid End With End Sub
Reply
Randy
Randy     RaveFromDave
This comment was minimized by the moderator on the site
Could not get this to work - please help, thanks :-)
Reply
Bozo
Bozo     Randy
This comment was minimized by the moderator on the site
"Please help" - do you expect an answer with such a vague request, and no effort to explain the problem?
Reply
Ka Yen
Ka Yen     RaveFromDave
This comment was minimized by the moderator on the site
thank you!
Reply
peenkwan
peenkwan     RaveFromDave
This comment was minimized by the moderator on the site
This is the perfect code thank you!!!!! no problems with moving from cell to cell and leaving a track of highlights and outlines behind.
Reply
betakos
betakos     Suresh Singh
This comment was minimized by the moderator on the site
i use insert table to my data and it does not change original colouring
Reply
Janet D
Janet D     Suresh Singh
This comment was minimized by the moderator on the site
This option is now available to non-coders in 2010 as long as you have the Developer tab. Under Developer, click Properties. Set EnableOutlining to True. Limitation: This appears to only apply to one worksheet at a time, although easy to set for other worksheets by clicking on tabs at bottom (while leaving the Properties dialog box open). Why Excel does not make this available under Options, well, it is Microsoft.
Reply
felix
felix
This comment was minimized by the moderator on the site
Hi, after putting in the code, the "undo" function no longer works. Can someone help?
Reply
Matthewwwww
Matthewwwww
This comment was minimized by the moderator on the site
Hi! I really like this code but like Suresh said, it removes original cell colors! How can I keep my original cell colors?? Please respond, thanks!
Reply
vipe
vipe
This comment was minimized by the moderator on the site
This works brilliantly. Is there a way of making it not show up on printing?
Reply
Will
Will
This comment was minimized by the moderator on the site
Just what I was looking for. Thank you!
Reply
Charles
Charles
This comment was minimized by the moderator on the site
To keep your cell color you will have to create a Conditional Formatting for the cells.
Reply
Mac
Mac
This comment was minimized by the moderator on the site
Any plans to add Mac functionality for Kutools?
Reply
Ton Hermes
Ton Hermes
This comment was minimized by the moderator on the site
How can I change the colors of the bars? Thnaks for this great solution. Regards, Ton
Reply
betakos
betakos
This comment was minimized by the moderator on the site
how can i stop it if i want it to stop??
Reply
listermoss
listermoss
This comment was minimized by the moderator on the site
This is the code I used for just the row (grey highlight): Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) 'Update 20140318 Static xRow If xRow "" Then With Rows(xRow).Interior .ColorIndex = xlNone End With End If pRow = Selection.Row xRow = pRow With Rows(pRow).Interior .ColorIndex = 15 .Pattern = xlSolid End With End Sub
Reply
Christina
Christina     listermoss
This comment was minimized by the moderator on the site
Copying and pasting your code did not work properly, but after fixing the syntax errors it worked amazingly! Thank you. Here is the working code for highlighting (yellow) the row only: Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) 'Update 20140318 Static xRow If xRow "" Then With Rows(xRow).Interior .ColorIndex = xlNone End With End If pRow = Selection.Row xRow = pRow With Rows(pRow).Interior .ColorIndex = 6 .Pattern = xlSolid End With End Sub
Reply
Aditya
Aditya
This comment was minimized by the moderator on the site
Bravo..exactly what I was looking for.....thanks a lot!!
Reply
SaltWater
SaltWater
This comment was minimized by the moderator on the site
When I reopen my workbook highlighted column from the prior session remains - and does change as I move around the worksheet in a new session. Is there a way to "clear" the prior session each time the workbook is opened?
Reply
mike_thomas
mike_thomas
This comment was minimized by the moderator on the site
This works if you don't have colors not in conditional formatting. Just add two more with loops. Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) 'Update 20140318 Static xRow Static xColumn If xColumn "" Then With Rows(xRow).Interior .ColorIndex = xlNone End With End If pRow = Selection.Row xRow = pRow With Rows(pRow).Interior .ColorIndex = 6 .Pattern = xlSolid End With With Rows(pRow - 1).Interior .ColorIndex = 0 .Pattern = x1Solid End With With Rows(pRow + 1).Interior .ColorIndex = 0 .Pattern = x1Solid End With End Sub
Reply
KSV
KSV
This comment was minimized by the moderator on the site
HOW CAN I MAKE IT POSSIBLE IF HAVE SHEET THAT IS ALREADY COLORED.
Reply
Vicente
Vicente
This comment was minimized by the moderator on the site
Great article!. Life saver for me. And if you want the actual cell to be highlighted in yellow and row/cells in grey, this is the code: Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) 'Update 20151216 Static xRow Static xColumn If xColumn "" Then With Columns(xColumn).Interior .ColorIndex = xlNone End With With Rows(xRow).Interior .ColorIndex = xlNone End With End If pRow = Selection.Row pColumn = Selection.Column xRow = pRow xColumn = pColumn With Columns(pColumn).Interior .ColorIndex = 15 .Pattern = xlSolid End With With Rows(pRow).Interior .ColorIndex = 15 .Pattern = xlSolid End With Selection.Interior.ColorIndex = 6 End Sub
Reply
Kamran
Kamran     Vicente
This comment was minimized by the moderator on the site
Correct code as follows: Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) 'Update 20151216 Static xRow Static xColumn If xColumn "" Then With Columns(xColumn).Interior .ColorIndex = xlNone End With With Rows(xRow).Interior .ColorIndex = xlNone End With End If pRow = Selection.Row pColumn = Selection.Column xRow = pRow xColumn = pColumn With Columns(pColumn).Interior .ColorIndex = 15 .Pattern = xlSolid End With With Rows(pRow).Interior .ColorIndex = 15 .Pattern = xlSolid End With Selection.Interior.ColorIndex = 6 End Sub [quote]Great article!. Life saver for me. And if you want the actual cell to be highlighted in yellow and row/cells in grey, this is the code: Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) 'Update 20151216 Static xRow Static xColumn If xColumn "" Then With Columns(xColumn).Interior .ColorIndex = xlNone End With With Rows(xRow).Interior .ColorIndex = xlNone End With End If pRow = Selection.Row pColumn = Selection.Column xRow = pRow xColumn = pColumn With Columns(pColumn).Interior .ColorIndex = 15 .Pattern = xlSolid End With With Rows(pRow).Interior .ColorIndex = 15 .Pattern = xlSolid End With Selection.Interior.ColorIndex = 6 End SubBy Vicente[/quote]
Reply
Chienph
Chienph
This comment was minimized by the moderator on the site
Hi. How can i high light entire row if one of cell is merge. I see kutool can make a line across a merge cell. Pl help me this solution
Reply
David
David
This comment was minimized by the moderator on the site
I changed your code from highlighting the interior to making red borders.
.BorderAround Weight:=xlThick, Color:=vbRed
I select a cell, the borders turns red. Great. I copy, select another cell, those borders turn red, but the clipboard clears. I cannot figure out how to use borders, and keep the copy/paste functionality.
Any suggestions?
Reply
Char
Char     David
This comment was minimized by the moderator on the site
can you share the whole code?
Reply
Arthur Stolzenbach
Arthur Stolzenbach     David
This comment was minimized by the moderator on the site
Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

'Update 20192021
Static xRow
If xRow <> "" Then
Rows(xRow).BorderAround Weight:=xlThin, Color:=vbBlack
End If
pRow = Selection.Row
xRow = pRow
Rows(pRow).BorderAround Weight:=xlThick, Color:=vbRed
End Sub
Reply
Report
nolan
nolan
This comment was minimized by the moderator on the site
how do you prevent the undo & redo buttons from being deleted with this macro not using the Kutools
Reply
crystal
crystal     nolan
This comment was minimized by the moderator on the site
Good Day,
Sorry about that. The undo & redo problem in the macro can't be solved.
Reply
Report
Jay
Jay
This comment was minimized by the moderator on the site
The VBA to highlight a single cell works but this one does not.
Reply
David
David
This comment was minimized by the moderator on the site
Thank you for posting this! It's very useful in check reconciliation via spreadsheet.
Reply
There are no comments posted here yet
Load More
Leave your comments
Guest
Posting as Guest
Login
×
Rate this post:
Reset
0  Characters
Suggested Locations