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, please do with following steps:

1. Hold down the ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications window.

2. Then choose your used worksheet from the left Project Explorer, double click it to open the Module, and then copy and paste following VBA code into the blank Module:

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

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

3. Then save and close this code 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. If you cannot find the Project Explorer Pane in the window, you can click View > Project Explorer to open it.

2. In the above code, you can change .ColorIndex = 6 color to other color you like.

3. This VBA code only apply to the worksheet you choose in the above step 2.

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

5. 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 want to highlight entire rows and columns of a range of cells, the code will not be available, but with the Kutools for Excel’s Reading Layout View, you can solve this problem quickly.

Kutools for Excel : with more than 300 handy Excel add-ins, free to try with no limitation in 60 days.

1. Select a cell or a range of cells in your worksheet. Click Kutools > Reading Layout to activate this feature. And the entire columns and rows of the selected cells are highlighted immediately. And it will be dynamically moved as the selected cells change. See screenshots:

Tip: You can set some attributions for this Reading Layout feature in the Reading Layout Settings dialog box, please click Kutools > Reading > Reading Layout Settings to open the Reading Layout Settings dialog box.

You can only highlight row of selected cell by choosing the Straight line option:

You can only highlight column of selected cell by choosing the Vertical line option:

Notes:

1. You can cancel this utility by unchecking the Reading Layout under Reading drop down list.

2. This Reading Layout View feature is applied to all of the worksheets within your workbook.

3. This Reading Layout View feature will disabled when you launch the workbook next time.

One click to highlight row and column of selected cell

Kutools for Excel includes more than 300 handy Excel tools. Free to try with no limitation in 60 days. Download the free trial now!

Recommended Productivity Tools

Office Tab

Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.

Kutools for Excel