How to highlight active row and column in Excel

In this tutorial, you will learn 3 different ways to dynamically highlight the row and column of a selected cell in Excel.

When viewing a large worksheet for a long time, you may eventually lose track of where your cursor is and which data you are looking at. To know exactly where you are at any moment, get Excel to automatically highlight the active row and column for you! Naturally, the highlighting should be dynamic and change every time you select another cell. Essentially, this is what we are aiming to achieve:

Highlighting active row and column in Excel

Auto-highlight row and column of selected cell with VBA

This example shows how you can highlight an active column and row programmatically with VBA. For this, we will be using the SelectionChange event of the Worksheet object.

First, you clear the background color of all cells on the sheet by setting the ColorIndex property to 0. And then, you highlight the entire row and column of the active cell by setting their ColorIndex property to the index number for the desired color.

Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Cells.Count > 1 Then Exit Sub Application.ScreenUpdating = False 'Clear the color of all cells Cells.Interior.ColorIndex = 0 With Target 'Highlight row and column of the selected cell .EntireRow.Interior.ColorIndex = 38 .EntireColumn.Interior.ColorIndex = 24 End With Application.ScreenUpdating = True End Sub

Customizing the code

If you'd like to customize the code for your needs, these small tips may come in handy:

  • Our sample code uses two different colors demonstrated in the above gif - color index 38 for row and 24 for column. To change the highlight color, just replace those with any ColorIndex codes of your choosing.
  • To get the row and column colored in the same way, use the same color index number for both.
  • To only highlight the active row, remove or comment out this line: .EntireColumn.Interior.ColorIndex = 24
  • To only highlight the active column, remove or comment out this line: .EntireRow.Interior.ColorIndex = 38

How to add the code to your worksheet

To have the code silently executed in the background of a specific worksheet, you need to insert it in the code window belonging to that worksheet, not in the normal module. To have it done, carry out these steps:

  1. In your workbook, press Alt + F11 to get to the VBA editor.
  2. In the Project Explorer on the left, you'll see a list of all open workbooks and their worksheets. If you don't see it, use the Ctrl + R shortcut to bring the Project Explorer window to view.
  3. Find the target workbook. In its Microsoft Excel Objects folder, double-click on the sheet in which you want to apply highlighting. In this example, it's Sheet 1.
  4. In the Code window on the right, paste the above code.
  5. Save your file as Macro-Enabled Workbook (.xlsm).

Highlight active row and column

Advantages: everything is done in the backend; no adjustments/customizations are needed on the user's side; works in all Excel versions.

Drawbacks: there are two essential downsides that make this technique inapplicable under certain circumstances:

  • The code clears background colors of all cells in the worksheet. If you have any colored cells, do not use this solution because your custom formatting will be lost.
  • Executing this code blocks the undo functionality on the sheet, and you won't be able to undo an erroneous action by pressing Ctrl + Z.

Highlight active row and column without VBA

The best you can get to highlight the selected row and/or column without VBA is Excel's conditional formatting. To set it up, carry out these steps:

  1. Select your dataset in which the highlighting should be done.
  2. On the Home tab, in the Styles group, click New Rule.
  3. In the New Formatting Rule dialog box, choose Use a formula to determine which cells to format.
  4. In the Format values where this formula is true box, enter one of these formulas:

    To highlight active row:

    =CELL("row")=ROW()

    To highlight active column:

    =CELL("col")=COLUMN()

    To highlight active row and column:

    =OR(CELL("row")=ROW(), CELL("col")= COLUMN())

    All the formulas make use of the CELL function to return the row/column number of the selected cell.

  5. Click the Format button, switch to the Fill tab, and select the color you like.
  6. Click OK twice to close both dialog windows.

If you feel like you need more detailed instructions, please see How to create formula-based conditional formatting rule.

For this example, we opted for the OR formula to shade both the column and row in the same color. That takes less work and is suitable for most cases.
Conditional formatting to highlight active row and column

Unfortunately, this solution is not as nice as the VBA one because it requires recalculating the sheet manually (by pressing the F9 key). By default, Excel recalculates a worksheet only after entering new data or editing the existing one, but not when the selection changes. So, you select another cell - nothing happens. Press F9 - the sheet is refreshed, the formula is recalculated, and the highlighting is updated.
Highlighting the selected column and row without VBA

To get the worksheet recalculated automatically whenever the SelectionChange event occurs, you can place this simple VBA code in the code module of your target sheet as explained in the previous example:

Private Sub Worksheet_SelectionChange(ByVal Target As Range) Target.Calculate End Sub

The code forces the selected range/cell to recalculate, which in turn forces the CELL function to update and the conditional formatting to reflect the change.

Advantages: unlike the previous method, this one does not impact the existing formatting you have applied manually.

Drawbacks: may worsen Excel's performance.

  • For the conditional formatting to work, you need to force Excel to recalculate the formula on every selection change (either manually with the F9 key or automatically with VBA). Forced recalculations may slow down your Excel. Since our code recalculates the selection rather than an entire sheet, a negative effect will most likely be noticeable only on really large and complex workbooks.
  • Since the CELL function is available in Excel 2007 and higher, the method won't work in earlier versions.

Highlight selected row and column using conditional formatting and VBA

In case the previous method slows down your workbook considerably, you can approach the task differently - instead of recalculating a worksheet on every user move, get the active row/column number with the help of VBA, and then serve that number to the ROW() or COLUMN() function by using conditional formatting formulas.

To accomplish this, here are the steps you need to follow:

  1. Add a new blank sheet to your workbook and name it Helper Sheet. The only purpose of this sheet is to store two numbers representing the row and column containing a selected cell, so you can safely hide the sheet at a later point.
  2. Insert the below VBA in the code window of the worksheet where you wish to implement highlighting. For the detailed instructions, please refer to our first example.
    Private Sub Worksheet_SelectionChange(ByVal Target As Range) Application.ScreenUpdating = False Worksheets("Helper Sheet").Cells(2, 1) = Target.Row Worksheets("Helper Sheet").Cells(2, 2) = Target.Column Application.ScreenUpdating = True End Sub

    The above code places the coordinates of the active row and column to the sheet named "Helper Sheet". If you named your sheet differently in step 1, change the worksheet name in the code accordingly. The row number is written to A2 and the column number to B2.

  3. In your target worksheet, select the entire dataset, and create a conditional formatting rule with the below formulas. The step-by-step guidance is provided in the above example.

And now, let's cover the three main use cases in detail.

How to highlight active row

To highlight the row where your cursor is placed at the moment, set up a conditional formatting rule with this formula:

=ROW()='Helper Sheet'!$A$2
Conditional formatting to highlight the active row

As the result, the user can clearly see which row is currently selected:
The selected row is automatically highlighted.

How to highlight active column

To highlight the selected column, feed the column number to the COLUMN function using this formula:

=COLUMN()='Helper Sheet'!$B$2
Conditional formatting to highlight the active column

Now, a highlighted column lets you comfortably and effortlessly read vertical data focusing entirely on it.
The selected column is automatically highlighted.

How to highlight active row and column

To get both the selected row and column automatically shaded in the same color, combine the ROW() and COLUMN() functions into one formula:

=OR(ROW()='Helper Sheet'!$A$2, COLUMN()='Helper Sheet'!$B$2)
Conditional formatting to highlight the active column and row

The relevant data is immediately brought into focus, so you can avoid misreading it.
The selected row and column are highlighted.

Advantages: optimized performance; works in all Excel versions

Drawbacks: the longest setup

That's how to highlight the column and row of a selected cell in Excel. I thank you for reading and look forward to seeing you on our blog next week!

Practice workbook for download

Highlighting active row and column (.xlsm file)

62 comments

  1. in 365m you are having highlight in view menu. just use this. no need of coding or formulas

  2. Hello,
    I used to have this tool in some documents using the conditional formatting solution. Suddenly it doesn't work. I actually have to press manually on F9 to see the change. What could have changed whereas I didn't change anything ? (or at least purposely)
    It looks like the Excel settings have changed on their own.
    Thanks for your feedback.
    JJ

    1. Hi! Changing the cell color and cell format does not cause Excel to automatically recalculate formulas, because the values in the cells are not changed.
      In addition, I can't see your Excel settings.

      1. its is included in excel now, called focus cell!

  3. Hi, why does removing the VBA code there is still a highlighted column and row. I am using mac office 2019. I have to remove the colors manually, is there another way to do so?

    1. Helo Chris!
      Mac Office 2019 does support Visual Basic for Applications (VBA), but there are some differences and limitations compared to the Windows version. Most VBA macros and add-ins developed for Office for Windows can be used in Office for Mac, but there are some differences in the object model and available commands.
      Office for Mac VBA apps are sandboxed, which restricts access to resources. ActiveX controls, commonly used in VBA projects on Windows, are not supported on Mac.

      1. Is it possible to do conditional formatting for mac office 2019? There is no option "Use a formula to determine which cells to format". I know this guide is based on Windows.

        1. Hi! In Office for Mac, use New formatting rule - Style - Classic - Use a formula to determine which cells to format.

  4. Hi

    how to sort data with multiple criteria with custom order in vba

  5. Thank you Svetlana and Team for sharing this and logical, clear explanations - and for making it accessible. I am eternally grateful you have saved me lots of embarrassing moments with bosses:-) lol. Than you!!Dennis

  6. Hi,

    Visited this old guide. Very helpful, thank you.

    I did encounter an issue in that this code will format the entire column top to bottom. This fills the worksheet and you will get an error message when inserting a new row indicating that formatted cells will fall outside the worksheet. What I ended up doing is not formatting the entire row but just from the selected cell upward. This works for me.

    See below. I format only the lines, not the color. That way I can still use color formatting to organize my workbook.

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
    Application.ScreenUpdating = False

    'Clear the lines of all cells
    Cells.Borders.LineStyle = xlNone

    With Selection
    x = .Column
    y = .Row
    End With
    'Variable for color setting as I use it multiple times.
    r = 3
    c = 3

    'Row borders color
    With Range(Target, Cells(y, 1))
    .Borders(xlEdgeTop).ColorIndex = r
    .Borders(xlEdgeBottom).ColorIndex = r
    End With

    With Range(Target, Cells(1, x))
    .Borders(xlEdgeRight).ColorIndex = c
    .Borders(xlEdgeLeft).ColorIndex = c
    End With

    Application.ScreenUpdating = True
    End Sub

    Hope this helps.

    Kind regards.

  7. It’s vanishing other cell color as well. Is there any way to highlight column/row without hampering other cell color?

  8. It is of great help and works like magic!

    Thanks a many..

    Love from India!

    Best Regards,
    Gauri.

  9. Tahnks so much, I think there is an error in one of the images, just below the text
    How to highlight active row
    To highlight the row where your cursor is placed at the moment, set up a conditional formatting rule with this formula:

    =ROW()='Helper Sheet'!$A$2

    The image shows different code - i.e. = CELL("row").... I think it should show as above =ROW().... thanks again. Just thought I'd share as it threw me for a bit.

    1. Hi Anthony,

      You are right - the formula in the screenshot was indeed incorrect (somehow got there from the previous example). Fixed, thank you very much for pointing to this!

  10. Just one comment.
    I'm using Microsoft® Excel® for Microsoft 365 MSO (Version 2404 Build 16.0.17531.20152) 64-bit under Windows 11
    I have used the Conditional Formatting solution.
    In your step 4, I have had to complete the 'Applies To' box.
    Very many thanks for a super tool.

  11. I have this macro in several workbooks to highlight the active row, and it works fine, expect for one thing. If there are multiple workbooks open with this in it, everytime I move to a new cell in one workbook, all of the other open workbooks jump to the corresponding row. This defeats the reason to use it in any additional workbooks that may be open at the same time. The highlight is constantly jumping around on all of the open workbooks. Does anyone have a solution for this situation? Thanks in advance for any help.

    1. Hi! Unfortunately, I do not have a solution to offer you. I have used the sample file from the above article and several other workbooks. I have not experienced any of the problems you describe.

  12. very helpful and simply explained at all.

  13. I want to store the macro in my PERSONL.xbls so I can use with any file I receive. What changes are necessary?

  14. Hello,
    Great support and great advices !
    One question though : it's indeed a pity to lose formatting cells with the VBA solution. But when I use the solution with the conditional formatting, I cannot do " copy paste" anymore ! Indeed, when I do "copy" from a cell or a selection, Excel runs the "highlight row and cell" conditional format where I click in Excel to paste what I want, and as a consequence, I guess because it's an action since I've done "Copy", Excel releases the datas copied and I cannot paste it ! :( Any ideas how to either keep formatting with the VBA solution or to have the copy/paste capability with conditional formatting ?
    Many thanks in advance !
    JJ

    1. Hi! I have not been able to reproduce your problem, and I have had no difficulty copying and pasting data using this solution and using conditional formatting. Try to give a better explanation of the issue.

      1. Hi ! Thanks a lot.
        Let me try to be clearer : I then have introduced in "Conditional formatting" the formula : "=OR(CELL("row")=ROW();CELL("col")=COLUMN())".
        Once I'm in the sheet. I select the cells I want to copy. Then the cells are with a "dotted line" around them to show what cells I want to copy (Excel does it automatically).
        As soon as I move the cursor, or that I go with the mouse in the cell where I want to paste, Excel stops showing those dotted lines around the cells that I want to copy, so as a consequence I lose the content and paste doesn't work. It's like I would do "Escape" once having selected the cells I want to copy.
        If you still can't reproduce this issue, happy to show around a Teams call or else. Because I really love this highlighting row and column solution, but of course not being able to copy/paste is a big issue.
        Using the VBA solution and losing the initial format of the sheet is also bad ...
        Thanks again for your support.

        1. Hi! I applied your conditional formatting rule in the sample file linked at the end of the article. There are no problems with copying.

          1. I see that it works indeed ! Wonderful ! Many thanks for your support, appreciated.

  15. Hello, great tutorial! Works amazingly.

    I wanted to ask though, is there a way to set it up so that when a column is highlighted, it is only the part of the column above (and including) the selected cell? Same thing for the rows as well, is it possible for the row to be highlighted from the left side of the table only up to and including the selected cell, without highlighting the entire row within the range selected?

    For example, if you select cell D5, only the ranges D1:D5 and A5:D5 are highlighted, rather than the highlighted cells extending beyond what is selected.

  16. hi! love this thank you for sharing. I like setting up macros for formulas etc.. that I use often and this would be one that I would like to have as a permanent macro in my "personal" file so that I can activate the macro to any sheet I use. do you have any suggestions on how to get that done?
    thanks!!

  17. This is the second time I've used your guide to breathe fresh air into my workbooks. Thank you for sharing this knowledge in such an accessible way!

  18. Thanks you so much for the simple and informative tutorial.

  19. This was so easy, even a mid-level, 60 year old excel user such as myself could do it!

    Thank You, Thank You, Thank You!

  20. does it work for any new sheet? or just for the one were is implemented? thanks

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)