Cookies help us deliver our services. By using our services, you agree to our use of cookies.
Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

How to auto-highlight row and column of active cell in Excel?

doc highlight row1

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


One click to highlight row and column of selected cell in Excel:

Kutools for Excel's Reading Layout utility helps you quickly highlight row and column of selected cell in Excel. See screenshot:

Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download the free trial Now!


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

doc-reading-layout3

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.

doc-reading-layout4

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.

doc highlight row1

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

doc highlight row1

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

doc highlight row1

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.


Office Tab - Tabbed Browsing, Editing, and Managing of Workbooks in Excel:

Office Tab brings the tabbed interface as seen in web browsers such as Google Chrome, Internet Explorer new versions and Firefox to Microsoft Excel. It will be a time-saving tool and irreplaceble in your work. See below demo:

Click for free trial of Office Tab!

Office Tab for Excel


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

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

Kutools for Excel

gold star1 Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!

gold star1 300 New Features for Excel, Make Excel Much Easy and Powerful:

  • Merge Cell/Rows/Columns without Losing Data.
  • Combine and Consolidate Multiple Sheets and Workbooks.
  • Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
  • Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,
  • More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets Tools...

Screen shot of Kutools for Excel

Say something here...
symbols left.
You are guest ( Sign Up? )
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.
    Alok Ghosh · 4 days 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...
  • To post as a guest, your comment is unpublished.
    Urska · 10 months 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
  • To post as a guest, your comment is unpublished.
    D · 11 months 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
    • To post as a guest, your comment is unpublished.
      crystal · 10 months 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
  • To post as a guest, your comment is unpublished.
    David · 11 months ago
    Thank you for posting this! It's very useful in check reconciliation via spreadsheet.
  • To post as a guest, your comment is unpublished.
    Jay · 1 years ago
    The VBA to highlight a single cell works but this one does not.