acantor Posted November 22, 2021 Report Share Posted November 22, 2021 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: 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. Quote Link to comment Share on other sites More sharing options...
rberq Posted November 23, 2021 Report Share Posted November 23, 2021 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 // Quote Link to comment Share on other sites More sharing options...
acantor Posted November 23, 2021 Author Report Share Posted November 23, 2021 That's good! Quote Link to comment Share on other sites More sharing options...
rberq Posted November 23, 2021 Report Share Posted November 23, 2021 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=" "/> Quote Link to comment Share on other sites More sharing options...
acantor Posted November 24, 2021 Author Report Share Posted November 24, 2021 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>"/> Quote Link to comment Share on other sites More sharing options...
rberq Posted November 24, 2021 Report Share Posted November 24, 2021 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. Quote Link to comment Share on other sites More sharing options...
acantor Posted November 24, 2021 Author Report Share Posted November 24, 2021 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 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.