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!

Office Tab Enable Tabbed Editing and Browsing in Office, and Make Your Work Much Easier...
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
  • Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
  • More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words...
  • Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum...
  • Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns...
  • Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Create Mailing List and Send Emails by Cell's Value...
  • Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
  • More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.

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

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.
kte tab 201905

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!
officetab bottom
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.
    Shaikh Wasim · 7 months 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
    • To post as a guest, your comment is unpublished.
      crystal · 7 months 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.
  • To post as a guest, your comment is unpublished.
    Alok Ghosh · 9 months 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.
      crystal · 8 months 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.
  • To post as a guest, your comment is unpublished.
    Urska · 1 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
  • To post as a guest, your comment is unpublished.
    D · 1 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
    • To post as a guest, your comment is unpublished.
      crystal · 1 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
  • To post as a guest, your comment is unpublished.
    David · 1 years 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.
  • To post as a guest, your comment is unpublished.
    nolan · 1 years ago
    how do you prevent the undo & redo buttons from being deleted with this macro not using the Kutools
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Good Day,
      Sorry about that. The undo & redo problem in the macro can't be solved.
  • To post as a guest, your comment is unpublished.
    David · 2 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?
  • To post as a guest, your comment is unpublished.
    Chienph · 2 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
  • To post as a guest, your comment is unpublished.
    Vicente · 3 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
    • To post as a guest, your comment is unpublished.
      Kamran · 3 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]
  • To post as a guest, your comment is unpublished.
    KSV · 4 years ago
    HOW CAN I MAKE IT POSSIBLE IF HAVE SHEET THAT IS ALREADY COLORED.
  • To post as a guest, your comment is unpublished.
    mike_thomas · 4 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
  • To post as a guest, your comment is unpublished.
    SaltWater · 4 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?
  • To post as a guest, your comment is unpublished.
    Aditya · 4 years ago
    Bravo..exactly what I was looking for.....thanks a lot!!
  • To post as a guest, your comment is unpublished.
    listermoss · 4 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
    • To post as a guest, your comment is unpublished.
      Christina · 2 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
  • To post as a guest, your comment is unpublished.
    betakos · 5 years ago
    how can i stop it if i want it to stop??
  • To post as a guest, your comment is unpublished.
    Ton Hermes · 5 years ago
    How can I change the colors of the bars?
    Thnaks for this great solution.

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

        now, how abot rows and columsn?
      • To post as a guest, your comment is unpublished.
        farfan · 4 years ago
        Oh! man, works like a charm!!! great
English English
en Englishar العربيةzh-CN 简体中文zh-TW 繁體中文nl Nederlandsfr Françaisde Deutschit Italianoja 日本語ko 한국어pt Portuguêsru Русскийes Español