Jump to content
Macro Express Forums

Challenge: a macro to make it easier to see the active cell in Excel


Recommended Posts

When navigating around Excel spreadsheets without a mouse using the four arrow keys, the cell you're interacting with has a rectangle around it. This rectangle provides a visual indicator of which cell has focus.

 

 

In recent versions of Excel, Microsoft has made the focus indicator fainter. As a result, when you're not using a mouse, spotting which cell you're interacting with can be challenging, especially when cells have borders around them:

 

image.png.5358a7b1dd63544a686b199756611bb2.png

 

Note the green rectangle around cell B5. Having trouble seeing it? Exactly!!!

 

 

Apparently, the faint focus indicator is hardwired into Excel, and can't be changed. Switching from Office's default visual theme helps, but not much.

 

 

Although there may not be a solution to this problem unless Microsoft decides to address it, perhaps we can come up with a temporary solution: a macro that draws attention to the focused cell.

 

 

The challenge: Write a Macro Express hotkey macro for Excel that helps a user spot which cell they are interacting with.

Link to comment
Share on other sites

In Excel 2010, these keystrokes enter the "format cells" dialog and change the current cell background color to red.  Stands out nicely.  Would probably be a different key sequence for different generations of Excel.  Also we would want a second macro to change the cell color back to No Color. 

 

I wanted to use "Background color" instead of "Fill effects" but, for reasons unknown, ALT-c doesn't work to get to "Background color".   It seems to be a feature of Excel that non-intuitive navigation is the rule.   Incidentally, Excel 2010 places a heavy border around the current cell instead of requiring an up-to-date eyeglass prescription like later Excel versions.

 

//  
Text Type (Simulate Keystrokes): <CTRLD>1<CTRLU>    // format cells
Delay: 250 milliseconds
Text Type (Simulate Keystrokes): p            // protection tab
Delay: 250 milliseconds
Text Type (Simulate Keystrokes): <ARROW LEFT>        // left to fill tab
Delay: 250 milliseconds
Text Type (Simulate Keystrokes): <ALTD>i<ALTU>        // fill effects
Delay: 250 milliseconds
Text Type (Simulate Keystrokes): <TAB><TAB>        // tabs and arrows to select fill colors
Delay: 250 milliseconds
Text Type (Simulate Keystrokes): <ARROW DOWN><ARROW DOWN><ARROW DOWN><ARROW DOWN><ARROW DOWN><ARROW DOWN><ARROW DOWN><ARROW DOWN>
Delay: 250 milliseconds
Text Type (Simulate Keystrokes): <ENTER>
Delay: 250 milliseconds
Text Type (Simulate Keystrokes): <TAB>
Delay: 250 milliseconds
Text Type (Simulate Keystrokes): <ARROW DOWN><ARROW DOWN><ARROW DOWN><ARROW DOWN><ARROW DOWN><ARROW DOWN><ARROW DOWN><ARROW DOWN>
Delay: 250 milliseconds
Text Type (Simulate Keystrokes): <ENTER>
Delay: 250 milliseconds
Text Type (Simulate Keystrokes): <TAB><TAB>
Delay: 250 milliseconds
Text Type (Simulate Keystrokes): <ENTER>    // apply colors
Delay: 250 milliseconds
Text Type (Simulate Keystrokes): <TAB><TAB><TAB><TAB>
Delay: 250 milliseconds
Text Type (Simulate Keystrokes): <ENTER>        // done
Delay: 250 milliseconds
Macro Return
//  

 

Link to comment
Share on other sites

This version works very fast (again, using Excel 2010.)  The hotkey to activate it is RightArrow.  The macro clears the background color (if any) of the current cell, TABs right to the next cell, and sets background color red.  Similar macros could be written for left/up/down arrows, so that the moved-to cell will be colored and others not colored.  In England, they will be coloured / not coloured. 

 

Note keystroke speed zero -- Excel is successfully buffering keystrokes, which helps the process run fast.  There is one problem with this macro -- notice that the macro TABs to the next cell rather than simulating right arrow.  That's because a simulated right arrow is the hotkey that starts the macro running again, and again, and again ....  Using TAB avoids the problem for left/right movement, but I'm not sure what to do about up and down movement.  

 

//  
// Highlight Excel cell by making its background red
Keystroke Speed: 0 milliseconds
// clear color of current cell, if any
Text Type (Simulate Keystrokes): <CTRLD>1<CTRLU> // format cells
Text Type (Simulate Keystrokes): pff             // assure "fill" tab
Text Type (Simulate Keystrokes): <ALTD>c<ALTU>   // background color
Text Type (Simulate Keystrokes): <ENTER>         // select no color
Text Type (Simulate Keystrokes): <TAB><TAB><TAB><TAB><TAB><ENTER> // tab to OK and exit
// arrow right to next cell
Text Type (Simulate Keystrokes): <TAB>   
// set color of next cell to red
Text Type (Simulate Keystrokes): <CTRLD>1<CTRLU> // format cells
Text Type (Simulate Keystrokes): pff             // assure "fill" tab
Text Type (Simulate Keystrokes): <ALTD>c<ALTU>   // background color
Text Type (Simulate Keystrokes): <ARROW DOWN><ARROW DOWN><ARROW DOWN><ARROW DOWN><ARROW DOWN><ARROW DOWN><ENTER> // select red
Text Type (Simulate Keystrokes): <TAB><TAB><TAB><TAB><TAB><ENTER> // tab to OK and exit
Macro Return
//  

 

<COMMENT Value=" "/>
<COMMENT Value="Highlight Excel cell by making its background red "/>
<KEYSTROKE SPEED Delay="0"/>
<COMMENT Value="clear color of current cell, if any "/>
<TEXT TYPE Action="0" Text="<CTRLD>1<CTRLU>" _COMMENT="format cells"/>
<TEXT TYPE Action="0" Text="pff" _COMMENT="assure \"fill\" tab"/>
<TEXT TYPE Action="0" Text="<ALTD>c<ALTU>" _COMMENT="background color"/>
<TEXT TYPE Action="0" Text="<ENTER>" _COMMENT="select no color"/>
<TEXT TYPE Action="0" Text="<TAB><TAB><TAB><TAB><TAB><ENTER>" _COMMENT="tab to OK and exit"/>
<COMMENT Value="arrow right to next cell"/>
<TEXT TYPE Action="0" Text="<TAB>"/>
<COMMENT Value="set color of next cell to red"/>
<TEXT TYPE Action="0" Text="<CTRLD>1<CTRLU>" _COMMENT="format cells"/>
<TEXT TYPE Action="0" Text="pff" _COMMENT="assure \"fill\" tab"/>
<TEXT TYPE Action="0" Text="<ALTD>c<ALTU>" _COMMENT="background color"/>
<TEXT TYPE Action="0" Text="<ARROW DOWN><ARROW DOWN><ARROW DOWN><ARROW DOWN><ARROW DOWN><ARROW DOWN><ENTER>" _COMMENT="select red"/>
<TEXT TYPE Action="0" Text="<TAB><TAB><TAB><TAB><TAB><ENTER>" _COMMENT="tab to OK and exit"/>
<MACRO RETURN/>
<COMMENT Value=" "/>

 

 

Link to comment
Share on other sites

Here are three solutions.

 

The first is inspired by rberq's. Originally, my version of his script changed the Pattern Style twice: change the pattern, wait a second, and change the pattern back to its default. My "aha" moment was realizing the second half of the script was unnecessary. All I had to do was change the pattern once, and then undo.

 

// In Excel 2019, the focus ring is harder to spot than in previous versions.
// Temporarily change "Pattern Style" of the selected cells
 
Text Type (Simulate Keystrokes): <CTRLD>1<CTRLU> // Hotkey to show "Format Cells" window
Delay: 100 milliseconds // (May need a longer delay for "Format Cells" to appear)
 
Window Resize: Format Cells Size: 0, 0 // Shrink size to zero so script is less "flashy"
 
Text Type (Simulate Keystrokes): p // Navigate directly to the "Protection" tab, then left to the "Fill" tab
Text Type (Simulate Keystrokes): <ARROW LEFT>
 
Text Type (Simulate Keystrokes): <ALT>p // Navigate to "Pattern Style"
Text Type (Simulate Keystrokes): <ARROW RIGHT><ENTER> // Choose the first pattern to the right
Text Type (Simulate Keystrokes): <TAB><ENTER> // Finalize the choice
 
Delay: 800 milliseconds // Flash the pattern for this long
 
Text Type (Simulate Keystrokes): <CONTROL>z // Undo the above action

<COMMENT Value="In Excel 2019, the focus ring is harder to spot than in previous versions."/>
<COMMENT Value="Temporarily change \"Pattern Style\" of the selected cells"/>
<COMMENT/>
<TEXT TYPE Action="0" Text="<CTRLD>1<CTRLU>" _COMMENT="Hotkey to show \"Format Cells\" window"/>
<DELAY Flags="\x02" Time="100" _COMMENT="(May need a longer delay for \"Format Cells\" to appear)"/>
<COMMENT/>
<WINDOW RESIZE Option="\x01" Title="Format Cells" Partial="FALSE" Wildcards="FALSE" Method="\x00" Width="0" Height="0" _IGNORE="0x0006" _COMMENT="Shrink size to zero so script is less \"flashy\""/>
<COMMENT/>
<TEXT TYPE Action="0" Text="p" _COMMENT="Navigate directly to the \"Protection\" tab, then left to the \"Fill\" tab"/>
<TEXT TYPE Action="0" Text="<ARROW LEFT>"/>
<COMMENT/>
<TEXT TYPE Action="0" Text="<ALT>p" _COMMENT="Navigate to \"Pattern Style\""/>
<TEXT TYPE Action="0" Text="<ARROW RIGHT><ENTER>" _COMMENT="Choose the first pattern to the right"/>
<TEXT TYPE Action="0" Text="<TAB><ENTER>" _COMMENT="Finalize the choice"/>
<COMMENT/>
<DELAY Flags="\x02" Time="800" _COMMENT="Flash the pattern for this long"/>
<COMMENT/>
<TEXT TYPE Action="0" Text="<CONTROL>z" _COMMENT="Undo the above action"/>

 

For the second solution, you must change a setting in Control Panel > Mouse > Pointer Options.  "Show location of pointer when I press the CTRL key" must be checked. The script makes a cell visible by flashing the pointer indicator three times.

 

// In Excel 2019, the focus ring is harder to spot than in previous versions.
// In "Mouse Properties" check "Show location of pointer when I press the CTRL key"
// This script uses the feature to flash the mouse pointer near the focused cell.
Get Mouse Position into (%x%, %y%) Relative to Current Window // Get current mouse position
 
Mouse Move: To the Text Cursor Position
Mouse Move: 30, 10 Relative to Last Position // Nudge the pointer toward the centre of the cell
 
Text Type (Simulate Keystrokes): <CTRLD>
Text Type (Simulate Keystrokes): <CTRLU>
Delay: 300 milliseconds
Text Type (Simulate Keystrokes): <CTRLD>
Text Type (Simulate Keystrokes): <CTRLU>
Delay: 300 milliseconds
Text Type (Simulate Keystrokes): <CTRLD>
Text Type (Simulate Keystrokes): <CTRLU>
 
Mouse Move: %x%, %y% Relative to Current Window // Restore original mouse position

<COMMENT Value="In Excel 2019, the focus ring is harder to spot than in previous versions."/>
<COMMENT Value="In \"Mouse Properties\" check \"Show location of pointer when I press the CTRL key\""/>
<COMMENT Value="This script uses the feature to flash the mouse pointer near the focused cell."/>
<GET MOUSE POSITION Option="\x01" X="%x%" Y="%y%" _COMMENT="Get current mouse position"/>
<COMMENT/>
<MOUSE MOVE Option="\x00" X="0" Y="0" _PROMPT="0x000A"/>
<MOUSE MOVE Option="\x03" X="30" Y="10" _PROMPT="0x000A" _COMMENT="Nudge the pointer toward the centre of the cell"/>
<COMMENT/>
<TEXT TYPE Action="0" Text="<CTRLD>"/>
<TEXT TYPE Action="0" Text="<CTRLU>"/>
<DELAY Flags="\x02" Time="300"/>
<TEXT TYPE Action="0" Text="<CTRLD>"/>
<TEXT TYPE Action="0" Text="<CTRLU>"/>
<DELAY Flags="\x02" Time="300"/>
<TEXT TYPE Action="0" Text="<CTRLD>"/>
<TEXT TYPE Action="0" Text="<CTRLU>"/>
<COMMENT/>
<MOUSE MOVE Option="\x02" X="%x%" Y="%y%" _PROMPT="0x000A" _COMMENT="Restore original mouse position"/>

 

The third solution shows the current cell by selecting and deselecting the entire row, and then selecting and deselecting the entire column. The point where the two "lines" intersect is the active cell.

// In Excel 2019, the focus ring is harder to spot than in previous versions.
// Select the row, then select the column. The active cell is where the two "lines" meet
 
Text Type (Simulate Keystrokes): <ESC> // If a cell is being edited, exit edit mode.
 
Text Type (Simulate Keystrokes): <SHIFT><SPACE> // Select the entire row, pause, then deselect
Delay: 900 milliseconds
Text Type (Simulate Keystrokes): <ARROW RIGHT><ARROW LEFT>
 
Text Type (Simulate Keystrokes): <CONTROL><SPACE> // Select the entire column, pause, and deselect
Delay: 900 milliseconds
Text Type (Simulate Keystrokes): <ARROW RIGHT><ARROW LEFT>

<COMMENT Value="In Excel 2019, the focus ring is harder to spot than in previous versions."/>
<COMMENT Value="Select the row, then select the column. The active cell is where the two \"lines\" meet"/>
<COMMENT/>
<TEXT TYPE Action="0" Text="<ESC>" _COMMENT="If a cell is being edited, exit edit mode."/>
<COMMENT/>
<TEXT TYPE Action="0" Text="<SHIFT><SPACE>" _COMMENT="Select the entire row, pause, then deselect"/>
<DELAY Flags="\x02" Time="900"/>
<TEXT TYPE Action="0" Text="<ARROW RIGHT><ARROW LEFT>"/>
<COMMENT/>
<TEXT TYPE Action="0" Text="<CONTROL><SPACE>" _COMMENT="Select the entire column, pause, and deselect"/>
<DELAY Flags="\x02" Time="900"/>
<TEXT TYPE Action="0" Text="<ARROW RIGHT><ARROW LEFT>"/>

 

 

Link to comment
Share on other sites

1 hour ago, acantor said:

My "aha" moment was realizing the second half of the script was unnecessary. All I had to do was change the pattern once, and then undo.

 

Clever.  I didn't think of "undo".

 

I like your third solution best, because there is no need to navigate through secondary panels.  Just a couple quick keystrokes and it's done.  In case the view has been previously shifted so the active cell is not visible, adding the ArrowRight-Arrow left before selecting row and column will bring it back into view.

Link to comment
Share on other sites

1 hour ago, rberq said:

In case the view has been previously shifted so the active cell is not visible, adding the ArrowRight-Arrow left before selecting row and column will bring it back into view.

 

It's my favourite as well, and the most effective. I might experiment with your refinement to see if it improves things.

 

I thought of a fourth solution this afternoon. which I thought would be the best. But when I started using it on real spreadsheets, I noted the active cell was too inconspicuous when the surrounding cells were certain colours. But it's an interesting concept:

 

Text Type (Simulate Keystrokes): <ESC> // If a cell is being edited, exit edit mode.
 
Text Type (Simulate Keystrokes): <CONTROL>a<CONTROL>a // Select the spreadsheet. (Must press Ctrl+A twice to capture everything)
Delay: 1000 milliseconds // While spreadsheet is selected, the active cell is a different colour
Text Type (Simulate Keystrokes): <ARROW RIGHT><ARROW LEFT> // Deselect

 

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...