Highlight the Active Row and Column in a Data Range in Excel

One of the Excel queries I often get is – “How to highlight the Active Row and Column in a data range?”

And I got one last week too.

Reader Query - How to Highlight Active Row and Column

So I decided to create a tutorial and a video on it. It will save me some time and help the readers too.

Below is a video where I show how to highlight the active row and column in Excel.

In case you prefer written instructions, below is a tutorial with exact steps on how to do it.

Let me first show you what we are trying to achieve.

A Demo to Show how to highlight the active row and column on selection change

In the above example, as soon as you select a cell, you can see that the row and column also get highlighted. This can be helpful when you’re working with a large dataset and can also be used in Excel Dashboards.

Now let’s see how to create this functionality in Excel.

Download the Example File

Highlight the Active Row and Column in Excel

Here are the steps to highlight the active row and column on selection:

  • Select the data set in which you to highlight the active row/column.
  • Go to the Home tab.
  • Click on Conditional Formatting and then click on New Rule.
  • In the New Formatting Rule dialog box, select “Use a formula to determine which cells to format”.
  • In the Rule Description field, enter the formula: =OR(CELL(“col”)=COLUMN(),CELL(“row”)=ROW())
  • Click on the Format button and specify the formatting (the color in which you want the row/column highlighted).
  • Click OK.

The above steps have taken care of highlighting the active row and active column (with the same color) whenever there is a selection change event.

However, to make this work, you need to place a simple VBA code in the backend.

Here is the VBA code that you can copy and paste (exact steps also listed below):

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Application.CutCopyMode = False Then
Application.Calculate
End If
End Sub

The above VBA code is run whenever there is a selection change in the worksheet. It forces the workbook to recalculate, which then forces the conditional formatting to highlight the active row and the active column. Normally (without any VBA code) a worksheet refreshes only when there is a change in it (such as data entry or edit).

Also, an IF statement is used in the code to check if the user is trying to copy paste any data in the sheet. During copy paste, the application is not refreshed and it is allowed.

Here are the steps to copy this VBA code in the backend:

  • Go to the Developer tab (can’t find the developer tab? – read this).Developer tab in the ribbon
  • Click on Visual Basic.Visual Basic icon in the Ribbon
  • In the VB Editor, on the left, you will see the project explorer that lists all the open workbooks and the worksheets in it. If you can’t see it, use the keyboard shortcut Control + R.Vb Editor Project Explorer - Highlight Selected Row or Column
  • With your workbook, double-click on the sheet name in which you have the data. In this example, the data is in Sheet 1 and Sheet 2.
  • In the code window, copy and paste the above VBA code. You’ll have to copy and paste the code for both sheets if you want this functionality in both sheets.Vb Code in the backend to highlight the active row and column in Excel
  • Close the VB Editor.

Since the workbook has VBA code in it, save it with a .XLSM extension.

Download the Example File.

Note that in the steps listed above, the active row and column would get highlighted with the same color. If you want to highlight the active row and column in different colors, use the below formulas:

  • =COLUMN()=CELL(“col”)
  • =CELL(“row”)=ROW()

In the download file provided with this tutorial, I have created two tabs, one each for single color and dual color highlighting.

Since these are two different formulas, you can specify two different colors.

Useful Notes:

  1. This method would not impact any formatting/highlighting you have done manually to the cells.
  2. Conditional formatting is volatile. If you use it on very large datasets, it may lead to a slow workbook.
  3. The VBA code used above would refresh the workbook every time there is a change in selection.
  4. CELL Function is available in Excel 2007 and above version for Windows and Excel 2011 and above for Mac. In case you’re using an older version, use this technique by Chandoo.

Want to Level-up your Excel Skills? Consider joining one of my Excel courses:

You May Also Like the Following Excel Tutorials:

  • Kurt says:

    I followed all the steps but no highlight is seen.
    Not sure what I’m doing wrong.
    The example file works for me even though it popped up an VB error, oddly only the 1st time when I opened it.

  • Kara says:

    I downloaded the excel example (and from what I can tell) – everything is the same. It’s still not working. I assume since saving as a .XLSM extension is not an option it’s set as a default??

    • Kara says:

      Never mind – I read other comments and typed in everything manually. Works like a charm!!

      Also, when typing in the conditional formatting code use ALL CAPS. 🙂

      Thank you for this!!

  • Frank says:

    Thanks for sharing this. I only have a problem when i’m using 2 excel windows side by side. Because when I swicht to the other window the cell formating in the first window changes. I there any solution for this?

  • Alex says:

    Hi, it is not working for me, I have Office 365 and have enabled the macros copying all the details but nothing gets highlighted. Any idea what I’m doing wrong?

  • David says:

    Doesn’t work for me. VBA code is identical, conditional formatting is identical. It’s Excel 2016. Any suggestions?

  • Martin says:

    Hi,

    Is there a way to make this work with a dynamic selection ? I would like this to work after I paste in a new set of data/selection (The selection will have the same columns, but the rows varies.

    Thanks

  • Jan says:

    =OR(CELL(“COL”)=COLUMN(),CELL(“ROW”)=ROW()) worked for me instead of =OR(CELL(“col”)=COLUMN(),CELL(“row”)=ROW())

  • Tais Malheiro says:

    Hi, I loved this highlight, but I need to keep using the “COPY” and “PAST” function as numbers and formulas. It is possible?

    • Sumit Bansal says:

      You can use the following VBA code:

      Private Sub Worksheet_SelectionChange(ByVal Target As Range)
      If Application.CutCopyMode = False Then
      Application.Calculate
      End If
      End Sub

  • Rob Phillips says:

    Hi Sumit,
    I am trying to use my spreadsheet with your modification. A problem I have found is that although I can copy a range of cells which are highlighted as normal, the paste function is greyed out. Ctrl+V also doesn’t work. Any ideas why?

  • Rob Phillips says:

    Me again,
    Maybe I should have read the instructions more carefully!! ;-)) I missed out the VBA code. Added it and now working brilliantly.

  • Rob Phillips says:

    Hi again, Just discovered that it works if I just select a cell and then press either page up or page down.

  • Rob Phillips says:

    Hi Sumit, I have tried to introduce this trick into a spreadsheet using Excel 2016. I used the conditional format method without the macro. Is it necessary to write the macro as well? The only way I can get it to work is to select a cell, press F2, and then press enter. In your example it is not necessary to go the F2 then enter route. Is there an option setting I am missing? Something to do with automatic screen updating?

  • Raja Sekhar says:

    General Copy and Past not working

    • Mauro says:

      is there a way to fix it? and make genera copy and paste work?

      • Sumit Bansal says:

        Use the following VBA code:

        Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        If Application.CutCopyMode = False Then
        Application.Calculate
        End If
        End Sub

  • Sunil Goel says:

    Like the tip very much. but it is not working for me. i am using office 2010.
    cells remain without highlight. cannot make out what’s wrong.

    • Sumit Bansal says:

      Hey Sunil, Did you copy the formula as is? I noticed that the quotation marks get copied in a different format. Try entering the formula manually and it should work

      • Sunil Goel says:

        great. after few hits and trial it worked. now to replicate it in all my worksheets??? any shortcuts or just copy the conditional formatting and vba to the other sheets.
        Thanks for the help – greatly appreciated.

        • Sunil Goel says:

          Hi Sumit, is there any way to apply this to all worksheets in a workbook? Also, how to make it easily applicable to a new workbook?
          thanks

      • ponypoor says:

        Thanks – had to type it out myself (the quotations were the issue), works like a charm on my huge spreadsheets.

  • miaousse says:

    Nice trick

    to the VBA i have added some lines.
    With Application
    .ScreenUpdating = False
    .EnableEvents = False
    .Calculate
    .ScreenUpdating = True
    .EnableEvents = True
    End With

    screenupdating will help speeding up the highlighting
    enableevents will prevent the triggering Worksheet_Change

  • >