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.
For more Excel tips and tricks like this, check out our Video Tutorials page.
Conversation
As you comment, let's keep the discussion friendly and respectful to add value to the conversation.
Lorem ipsum dolor sit amet, consectetur adipiscing elit. Suspendisse varius enim in eros elementum tristique. Duis cursus, mi quis viverra ornare, eros dolor interdum nulla, ut commodo diam libero vitae erat. Aenean faucibus nibh et justo cursus id rutrum lorem imperdiet. Nunc ut sem vitae risus tristique posuere. uis cursus, mi quis viverra ornare, eros dolor interdum nulla, ut commodo diam libero vitae erat. Aenean faucibus nibh et justo cursus id rutrum lorem imperdiet. Nunc ut sem vitae risus tristique posuere.