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

23 comments

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

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

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

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

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

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

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

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

  9. Hi all,

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

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

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

    Regards

    Buvana

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

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

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

  15. 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)

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