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.
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.

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!



































