Highlight the active row and column in an Excel worksheet

Ever lose track of where you are in a worksheet?

It's a common problem when navigating large datasets. You're flicking between cells, switching applications, and your eyes are darting between places.

You're handling sensitive data and want to be sure you're focusing on the correct record, but the further away you get from the row and column headers — the worse it gets.

You wish there were a better solution than meticulously sliding your index finger across the screen.

Unfortunately, Excel doesn't have a built-in feature for this, but there is a workaround.

Do the following:

1. Select a range.
2. Go to Home ＞ Conditional Formatting ＞ New Rule.
3. Select 'Use a formula to determine which cells to format'.
4. Input =𝙾𝚁(𝙲𝙴𝙻𝙻("𝚌𝚘𝚕")=𝙲𝙾𝙻𝚄𝙼𝙽(),𝙲𝙴𝙻𝙻("𝚛𝚘𝚠")=𝚁𝙾𝚆()).
5. Select Format and choose a fill colour.
6. Select OK twice.

You can now highlight the active row and column of the cell you're in by pressing F9 to recalculate the workbook. The upside of this method is there's no VBA. The downside is it isn't automatic.

However, adding some VBA code allows you to create an event that automatically performs this recalculation:

1. Press Alt + F11 to open the VBA Editor.
2. Open the relevant sheet object.
3. Input the code:

𝙿𝚛𝚒𝚟𝚊𝚝𝚎 𝚂𝚞𝚋 𝚆𝚘𝚛𝚔𝚜𝚑𝚎𝚎𝚝_𝚂𝚎𝚕𝚎𝚌𝚝𝚒𝚘𝚗𝙲𝚑𝚊𝚗𝚐𝚎(𝙱𝚢𝚅𝚊𝚕 𝚃𝚊𝚛𝚐𝚎𝚝 𝙰𝚜 𝚁𝚊𝚗𝚐𝚎)
𝙸𝚏 𝙰𝚙𝚙𝚕𝚒𝚌𝚊𝚝𝚒𝚘𝚗.𝙲𝚞𝚝𝙲𝚘𝚙𝚢𝙼𝚘𝚍𝚎 = 𝙵𝚊𝚕𝚜𝚎 𝚃𝚑𝚎𝚗
𝙰𝚙𝚙𝚕𝚒𝚌𝚊𝚝𝚒𝚘𝚗.𝙲𝚊𝚕𝚌𝚞𝚕𝚊𝚝𝚎
𝙴𝚗𝚍 𝙸𝚏
𝙴𝚗𝚍 𝚂𝚞𝚋

The active row and column now highlight automatically as you navigate the worksheet range.

#exceleration #excel #globalexcelsummit

---

The Global Excel Summit is the world's largest virtual gathering of Microsoft Excel users and experts.

Find us on:

[dara_]

Step 01
=OR(COLMUN=CELL("col"), ROW=CELL("row"))
Step 02
Right Click on Sheet Tab - View Code - Type - Target.Calculate

[scampydog]

Works great for me. (Edit - I just noticed if I dble click random cell, then click one I want, it does it, so can skip F9 - very fast moving around) What could be done to reverse as fast -I can't see a quick way. So at moment just select empty line in top row and leave there. Otherwise can leave where is doesn't really matter in practice

[alaamahdi]

Great but and a big BUT, the function of copy paste no longer works when doing it, if someone has any advice?

[sassygirl]

How to remove highlighted cell only without removing other formulas? Thank you.

[shuska]

It only works if I move to another sheet and than come back to the inital sheet where I have this formatting. So it is very slow.

[vadodarajilla]

After doing this, copy and paste does not work !!!

[LetsUtubeNow]

How shall I remove the formula after applying it

[arwildo]

If error try this :


* change, with ;

[mnhusin]

formula contain error, i believe i type as it is
edit: the comma (, ) in the middle of formula should be (;) in my excel

[Muhammad.Irsyad]

=OR(CELL('col')=COLUMN(), CELL('row')=ROW()) thanksme later

[md.abuzardar]

After I finish my work, I close this excel sheet. Then I open it again later, in that time the Highlight Active Cell format doesn't work. So, what's the permanent solution in this case?

[mundheralrashdi]

so complicated...
so Simple: Just click (Shift + Space) for row.
(Ctrl + Space) for Column.