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)

Table of contents

42 comments

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

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

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

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

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

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

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

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

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

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

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

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

  9. This was actually my maiden effort at inserting a VBA script, so thank you all for creating this.

    However, if I have a cell highlighted to allow me to circle back and look at an issue later, this script will delete that highlight. Is there a way to have it highlight the row I am working with, and then revert to the prior formatting?

    Example: I have cell C24 highlighted in yellow as an item requiring further review. I arrow past row 24 on the way to somewhere else, the script highlights row 24 ... is there a way to make it return cell C24 to its prior yellow color?

    • Hi! Unfortunately, I don't quite understand your question. If you manually highlight a cell with a color, the macro does not remove that color. Please explain it in more detail.

  10. After (NO LIE) 4 or 5 hours of other instructions I found this. Easy and works. Thanks so much. Jim

  11. Hey, I've seen this before and thought it was neato but didn't know how to do it. Your instructions worked like a charm! thank you for sharing.

  12. Hi
    Codes worked well for a specific file. However, when applying them to two different Excel files, changes are synchronizing between sheets. Need help to separate rules for each sheet. Any advice appreciated."

  13. If your Excel file has an existing highlight do not use this vba code above!!!! It says "cells.interior.colorindex = 0". This will clear all the exisitng fill/highlight on your sheet!!!

  14. The VBA auto highlighter will not highlight over existing conditional formatting. Is there a way to include highlighting over rows with conditional formatting?

  15. This solution (Auto-highlight row and column of selected cell with VBA) works great. But if I am not an idiot, the function of highlighting row and column was before a decade or so a part of menue click in the older versions of Excel. Why are these usefull functions disappearing from Excel in later versions - this is crazy user-unfriendly.

  16. Hi everione, in order to conditionaly format just the selected cell i just replaced the function OR with AND so it works.

    Thanks for the article!!! It helped me a lot.

    Good luck.

  17. Hey, would there be any update on the undo functionality using VBA?
    We're trying to work around on this, please let us know if there are any updates.

  18. Hi,
    How to Highlight ONLY the Cell I Click ?
    Thanks

    • Hi,

      try this:

      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
      ActiveCell.Interior.ColorIndex = 8

      Application.ScreenUpdating = True
      End Sub

  19. Hi all,

    This is very helpful, thanks!
    Is there a way to apply this for multiple selected rows?

  20. Is it possible that my previously highlighted part will not change?

  21. How to hightlight rows only in current region except the header row by VBA?

    Regards

    Buvana

  22. Hi

    Works like a charms. Thank you.

    I was wondering thou - If i want to highlight only certain Column and rows not ENTIRE ... how to do the range

    For instance - highlight Columns from A to BE ; and ROWS only from 3 to 80

    So that the highlights doesnt go entire 1000000 rows ... and columns

    Is it possbile? How to do that? Range Rows Count ... i am clueless.

    Thank you

    Bojan

  23. Hi. I was wondering if this highlight function (for the active row and column of the selected cell) is readily available in your Excel add-on?

    Thanks.

    • Hello!
      Our Ultimate Suite for Excel does not include this feature. But there are 70+ professional tools with 300+ options, among which you can find useful ones for yourself. You can read more details on this page.

  24. Here is the problem I have - whenever I make changes to the cell and move on, I can't "undo". I have a feeling it's caused by the VBA formatting.

    • Hi Jason,

      Absolutely so. The VBA code that highlights the active row and column blocks the undo functionality on the sheet. This limitation is explicitly mentioned under Drawbacks.

  25. hi, thanks for this.

    I tried the first VBA but every time I move to a new cell I just get an error popup 'Compile error: Invalid outside procedure'. The spreadsheet is very simple (just a small csv) and not other formatting.

    I'm running Excel form Mac on 2021 apple silicon machine: excel normally works find, though I never use macros apart from this one try. Any suggestions?

    best,
    Scott

    • Hi Scott,

      This code (as well as most other codes on our blog) is for Windows. It won't work in Excel for Mac, sorry.

    • Hello!
      VBA on MacOS does not work with worksheet and workbook events, does not call system windows. There are also other differences. Therefore, not all macros will work. Use a virtual machine (Parallels Desktop)

  26. Hey Svetlana.. Hope you're well :)

    I tried highlighting rows and cols with the conditional formatting , it's not working even after pressing f9 key. Could you please suggest some idea. I wanted to highlight my rows and cols only with the help of conditional formatting

    • Hi Misha,

      It's hard to say what the problem might be without seeing your worksheet. You can download our practice workbook at the end of this post and examine the rule on the sheet named "Conditional formatting". To check how it works without VBA, delete the code from the module of the above-mentioned sheet. I've just re-tested it, and the F9 key works fine on my machine with Excel 365 installed.

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