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.

Read More... Free Download... Purchase...

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!

Read More... Free Download... Purchase...

49 Comments
Login

Sort by Newest
- Best
- Popular
- Newest
- Oldest

You are guest

or post as a guest, but your post won't be published automatically.

Loading comment... The comment will be refreshed after 00:00.

To post as a guest, your comment is unpublished.
Gues · 2 months ago
FAIL. Wiped out all my other cell formatting irreversibly. And doesn't work if the sheet is protected. FAIL.
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 2 months ago
  
  Hi Gues,
  The code does not work in a protected worksheet. If you want to keep the original cell formatting, please try the below code.
  The Reading Layout feature of Kutools is stable and handy, maybe you can download a free trial to have a try.
  
  Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) 'Update 202001106 Static xRow Static xColumn Dim xWs As Worksheet Dim xCRg, xCRg1, xRRg, xRRg1, xOHRg As Range Dim xAHRORg, xAHRORg1 As Range Dim xAWs As Worksheet Dim xFNum As Integer Dim xCll As Range On Error Resume Next Application.ScreenUpdating = False pRow = Selection.Row pColumn = Selection.Column Set xWs = ActiveWorkbook.Worksheets.Item("AutoHighlightRAndC") Set xAWs = Target.Worksheet If xWs Is Nothing Then Set xWs = ActiveWorkbook.Worksheets.Add xWs.Name = "AutoHighlightRAndC" xWs.Visible = xlSheetHidden xAWs.Activate End If If xColumn <> "" Then Set xCRg = Columns(xColumn) xCRg.Interior.ColorIndex = xlNone Set xCRg = Intersect(xCRg.Worksheet.UsedRange, xCRg) For xFNum = 1 To xCRg.Count Set xAHRORg1 = xAWs.Cells(xFNum, xColumn) Set xAHRORg = xWs.Cells(xFNum, xColumn) xAHRORg1.Interior.ColorIndex = xAHRORg.Interior.ColorIndex 'xCRg.Item(xFNum).Interior.ColorIndex = xWs.Columns(xColumn).Item(xFNum).Interior.ColorIndex Next Set xRRg = Rows(xRow) xRRg.Interior.ColorIndex = xlNone Set xRRg = Intersect(xCRg.Worksheet.UsedRange, xCRg) For xFNum = 1 To xRRg.Count Set xAHRORg1 = xAWs.Cells(xRow, xFNum) Set xAHRORg = xWs.Cells(xRow, xFNum) xAHRORg1.Interior.ColorIndex = xAHRORg.Interior.ColorIndex 'xRRg.Item(xFNum).Interior.ColorIndex = xWs.Rows(xRow).Item(xFNum).Interior.ColorIndex Next End If xRow = pRow xColumn = pColumn Set xCRg = Columns(pColumn) Set xCRg1 = Intersect(xCRg.Worksheet.UsedRange, xCRg) For xFNum = 1 To xCRg1.Count Set xOHRg = xWs.Cells(xFNum, pColumn) xOHRg.Interior.ColorIndex = xCRg1.Item(xFNum).Interior.ColorIndex Next Set xRRg = Rows(pRow) Set xRRg1 = Intersect(xRRg.Worksheet.UsedRange, xRRg) For xFNum = 1 To xRRg1.Count Set xCll = xWs.Cells(pRow, xFNum) xCll.Interior.ColorIndex = xRRg1.Item(xFNum).Interior.ColorIndex Next xCRg.Interior.ColorIndex = 34 xRRg.Interior.ColorIndex = 34 Application.ScreenUpdating = True End Sub
  
  Reply
To post as a guest, your comment is unpublished.
raguirre6776@gmail.com · 8 months ago
The Reading Layout button is grayed out and I cannot select. What am I missing here?
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 8 months ago
  
  Hi,
  Is the Worksheet Design turning on? If the Worksheet Design is turning on, the Reading View will turn gray. You need to turn it off to activate the Reading Layout.
  
  Reply
To post as a guest, your comment is unpublished.
robert white · 9 months ago
forgive me, i am a novice, how do i run this code in my worksheet?
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 8 months ago
  
  Hi,
  Right click the sheet tab and select View Code from the context menu. Copy and paste the code into the Sheet(Code) window. Go back to the worksheet and the code will run automatically.
  
  1494.png
  
  Reply
To post as a guest, your comment is unpublished.
Shaikh Wasim · 1 years ago
When i try the above code to highlight row & column, its nt working, also i tried another code which worked as i found it in below comments which is

Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

Cells.Interior.ColorIndex = 0
Target.EntireRow.Interior.ColorIndex = 22
Target.EntireColumn.Interior.ColorIndex = 22

it worked but cleared all my cell colors to white & highlight the active cell, Please help me on this @crystal
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 1 years ago
  
  Hi Shaikh Wasim,
  The VBA code in the post works well in my case. Which Excel version do you use? As the "color cleared" problem you memtioned, I can't help to solve it yet. Way not try the Reading Layout utility of Kutools for Excel? It won't make any changes to your worksheet. Thanks for commenting.
  
  Reply
To post as a guest, your comment is unpublished.
Alok Ghosh · 2 years ago
After pasting the VBA code I saved as Add-In but not working. I want to change the color of the intersection cell also...
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 1 years ago
  
  Good Day,
  You can change the highlight color by changing the .ColorIndex value. But the color of the intersection cell can't be changed alone.
  
  Reply
To post as a guest, your comment is unpublished.
Shushka · 2 years ago
Above VBA code did not work. I've tried the below code for highlighting both the selected column and row. It's shorter and easier

Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

Cells.Interior.ColorIndex = 0
Target.EntireRow.Interior.ColorIndex = 22
Target.EntireColumn.Interior.ColorIndex = 22

End Sub
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 2 years ago
  
  Dear Urska,
  Thank you for sharing.
  
  Reply
To post as a guest, your comment is unpublished.
D · 3 years ago
How can I change the VBA so the text color changes when the row is highlighted?

Also, if you want to just highlight a row without the column.. Here is the code changes.

Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
'Update 20140318 'Updated By dlsodders for
'Select Just Row
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 = 10
.Pattern = xlSolid
End With
End Sub
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 2 years ago
  
  Good Day,
  The following code can help you change the text color when the row is highlighted.
  
  Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
  Static xRow
  Static xCol
  Dim pRow As Long
  Dim pCol As Long
  If Target.Count = 1 Then
  If xRow <> "" Then
  With Rows(xRow)
  .Interior.ColorIndex = xlNone
  .Font.ColorIndex = xlAutomatic
  End With
  End If
  pRow = Selection.Row
  pCol = Selection.Font.ColorIndex
  xRow = pRow
  xCol = pCol
  With Rows(pRow)
  .Interior.ColorIndex = 10
  .Interior.Pattern = xlSolid
  .Font.Color = -16776961
  End With
  End If
  End Sub
  
  Reply
To post as a guest, your comment is unpublished.
David · 3 years ago
Thank you for posting this! It's very useful in check reconciliation via spreadsheet.
- Reply
To post as a guest, your comment is unpublished.
Jay · 3 years ago
The VBA to highlight a single cell works but this one does not.
- Reply
To post as a guest, your comment is unpublished.
nolan · 3 years ago
how do you prevent the undo & redo buttons from being deleted with this macro not using the Kutools
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 3 years ago
  
  Good Day,
  Sorry about that. The undo & redo problem in the macro can't be solved.
  
  Reply
To post as a guest, your comment is unpublished.
David · 3 years ago
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
- To post as a guest, your comment is unpublished.
  astollie@gmail.com · 1 years ago
  
  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
- To post as a guest, your comment is unpublished.
  Char · 2 years ago
  
  can you share the whole code?
  
  Reply
To post as a guest, your comment is unpublished.
Chienph · 3 years ago
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
To post as a guest, your comment is unpublished.
Vicente · 5 years ago
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
- To post as a guest, your comment is unpublished.
  Kamran · 4 years ago
  
  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 name="Vicente"]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[/quote]
  
  Reply
To post as a guest, your comment is unpublished.
KSV · 5 years ago
HOW CAN I MAKE IT POSSIBLE IF HAVE SHEET THAT IS ALREADY COLORED.
- Reply
To post as a guest, your comment is unpublished.
mike_thomas · 5 years ago
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
To post as a guest, your comment is unpublished.
SaltWater · 5 years ago
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
To post as a guest, your comment is unpublished.
Aditya · 6 years ago
Bravo..exactly what I was looking for.....thanks a lot!!
- Reply
To post as a guest, your comment is unpublished.
listermoss · 6 years ago
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
- To post as a guest, your comment is unpublished.
  Christina · 3 years ago
  
  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
To post as a guest, your comment is unpublished.
betakos · 6 years ago
how can i stop it if i want it to stop??
- Reply
To post as a guest, your comment is unpublished.
Ton Hermes · 6 years ago
How can I change the colors of the bars?
Thnaks for this great solution.

Regards, Ton
- Reply
To post as a guest, your comment is unpublished.
Mac · 6 years ago
Any plans to add Mac functionality for Kutools?
- Reply
To post as a guest, your comment is unpublished.
Charles · 6 years ago
To keep your cell color you will have to create a Conditional Formatting for the cells.
- Reply
To post as a guest, your comment is unpublished.
Will · 6 years ago
Just what I was looking for. Thank you!
- Reply
To post as a guest, your comment is unpublished.
vipe · 6 years ago
This works brilliantly. Is there a way of making it not show up on printing?
- Reply
To post as a guest, your comment is unpublished.
Matthewwwww · 6 years ago
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
To post as a guest, your comment is unpublished.
felix · 6 years ago
Hi, after putting in the code, the "undo" function no longer works. Can someone help?
- Reply
To post as a guest, your comment is unpublished.
Suresh Singh · 6 years ago
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
- To post as a guest, your comment is unpublished.
  Janet D · 5 years ago
  
  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
- To post as a guest, your comment is unpublished.
  betakos · 6 years ago
  
  i use insert table to my data and it does not change original colouring
  
  Reply
- To post as a guest, your comment is unpublished.
  RaveFromDave · 6 years ago
  
  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
  
  To post as a guest, your comment is unpublished.
  
  Ka Yen · 11 months ago
  
  thank you!
  
  Reply
  
  To post as a guest, your comment is unpublished.
  
  Randy · 3 years ago
  
  Could not get this to work - please help, thanks :-)
  
  Reply
  
  To post as a guest, your comment is unpublished.
  
  Bozo · 3 years ago
  
  "Please help" - do you expect an answer with such a vague request, and no effort to explain the problem?
  
  Reply
  
  To post as a guest, your comment is unpublished.
  
  MARIO · 4 years ago
  
  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
  
  To post as a guest, your comment is unpublished.
  
  calixco · 5 years ago
  
  you are a geniuos
  
  now, how abot rows and columsn?
  
  Reply
  
  To post as a guest, your comment is unpublished.
  
  farfan · 5 years ago
  
  Oh! man, works like a charm!!! great
  
  Reply

Login to your account

Password Reset