Note: The other languages of the website are Google-translated. Back to English
English English▼
X
en Englishar Arabichy Armenianzh-CN Chinese (Simplified)zh-TW Chinese (Traditional)cs Czechda Danishnl Dutchfr Frenchde Germanel Greekhu Hungarianid Indonesianga Irishit Italianja Japaneseko Koreanpl Polishpt Portuguesero Romanianru Russiansl Slovenianes Spanishsv Swedishth Thaitr Turkishuk Ukrainianvi Vietnamesecy Welsh

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 (53)
Rated 5 out of 5 · 1 ratings
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
0
0
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
0
0
farfan
farfan     RaveFromDave
This comment was minimized by the moderator on the site
Oh! man, works like a charm!!! great
Reply
0
0
calixco
calixco     RaveFromDave
This comment was minimized by the moderator on the site
you are a geniuos now, how abot rows and columsn?
Reply
0
0
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
0
0
Randy
Randy     RaveFromDave
This comment was minimized by the moderator on the site
Could not get this to work - please help, thanks :-)
Reply
0
0
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
0
0
Ka Yen
Ka Yen     RaveFromDave
This comment was minimized by the moderator on the site
thank you!
Reply
0
0
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
0
0
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
0
0
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
0
0
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
0
0
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
0
0
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
0
0
Will
Will
This comment was minimized by the moderator on the site
Just what I was looking for. Thank you!
Reply
0
0
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
0
0
Mac
Mac
This comment was minimized by the moderator on the site
Any plans to add Mac functionality for Kutools?
Reply
0
0
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
0
0
betakos
betakos
This comment was minimized by the moderator on the site
how can i stop it if i want it to stop??
Reply
0
0
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
0
0
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
0
0
Aditya
Aditya
This comment was minimized by the moderator on the site
Bravo..exactly what I was looking for.....thanks a lot!!
Reply
0
0
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
0
0
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
0
0
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
0
0
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
0
0
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
0
0
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
0
0
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
0
0
Char
Char     David
This comment was minimized by the moderator on the site
can you share the whole code?
Reply
0
0
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
0
0
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
0
0
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
0
0
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
0
0
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
0
0
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