by Svetlana Cheusheva, updated on
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:
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.
If you'd like to customize the code for your needs, these small tips may come in handy:
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:
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 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:
To highlight active row:
To highlight active 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.
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.
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.
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:
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.
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:
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.
And now, let's cover the three main use cases in detail.
To highlight the row where your cursor is placed at the moment, set up a conditional formatting rule with this formula:
As the result, the user can clearly see which row is currently selected:
To highlight the selected column, feed the column number to the COLUMN function using this formula:
Now, a highlighted column lets you comfortably and effortlessly read vertical data focusing entirely on it.
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)
The relevant data is immediately brought into focus, so you can avoid misreading it.
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!
Highlighting active row and column (.xlsm file)
Table of contents