What it does
Shows an auto-complete search form to look for cell values across columns or across rows and jump to the first matching value or use conditional formatting to highlight all matching values.

When to use it
When you want to search for a value using auto-complete (potentially not knowing the exact value you’re looking for) and jump to the first matching value in the row or column.  Alternatively, you can highlight cells with matching values using conditional formatting.

Why to use it
This macro upgrades the find box to use auto-complete for searching and gives you the option to highlight values matching the value you’re searching for.

Default shortcut
None

Other Details

• Category: Productivity / Navigation
• Difficulty: 2/5
• Usage/frequency: 5/5
• Automation factor: 4/5 (estimated 30 seconds saved each time used)
• Type: Shortcut
• Date added: 9/20/2023
• Tags: search, auto complete, find, highlight

Related Macros
None

Other Articles
None

None

Prerequisites
None

Instructions
Run the Search Cells macro.  It will prompt you with a search form offering you suggestions of values as you type.  Use the up and down arrows (or click with the mouse) to select a suggested value to jump to.  You can search either across columns (within the selected row – default) or across rows (within the selected column).

The default mode is searching and jumping to the first matching cell value.  Alternatively, you can add conditional formatting to cells with matching values (click the formatting toggle button).  To clear the conditional formatting, simply run the Search Cells macro (with the conditional formatting option enabled) and it will clear.

Press the Escape key to clear the search box.  If the search box is empty when you press the Escape key, the search box will close.  You can also clear the search box by pressing the X icon to the right of the search box.

There are three icons to the right of the search box that will sort the search results either ascending, descending, or in the order they are displayed in the edit links dialog box.