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