What it does
Uses picker prompt to apply helpful conditional formatting settings, including those native to Excel and options unique to the XLEV8 add-in.  Options include adding banding (alternative row fill colors), variance font colors, highlighting blanks, errors, duplicates, hard-coded values or formula parts, and several others.

When to use it
When you want to help analyze your data, find errors easily, or add polish to your files.

Why to use it
It’s an easy way to apply helpful conditional formatting settings that help you and others work with your data.

Default shortcut
None

Other Details

• Category: Formatting / Polishing
• Difficulty: 2/5
• Usage/frequency: 3/5
• Automation factor: 3/5 (estimated 20 seconds saved each time used)
• Type: Shortcut
• Date added: 2/23/2018
• Tags: conditional, formats, picker, blanks, errors, banding

Related Macros
[A] Add Banding
[V] Red Green CF Variance (adds red/green fonts for amounts below/above zero)
[G] Good Bad Check Format (adds red/green fill color for amounts inside/outside of thresholds)
[R] CF Highlight Hard Coded Values (highlights hard-coded values – no formulas)
[F] CF Highlight Hard Coded Formulas (highlights cells with hard-coded formula parts)
[L] CF Highlight Text Lengths (highlights cells with a specified text length)
[B] CF Highlight Blanks (highlights cells with blank values)
[H] CF Difference Highlight (highlights cells with different values than a corresponding range)
[E] CF Highlight Errors (highlights cells with formula error results)
[O] Toggle Cell Tracker (highlights the active cell, row, and column)
[P] Pause Conditional Formatting (toggles a pause for conditional formats on the active sheet or all sheets)
[N] Set Conditional Formatting Defaults (allows you to define default colors used in the macros/options above)

Note: most of the related conditional formatting macros above will be covered in the instructions below.  The picker option code is displayed in the [brackets].

Other Articles
None

Conditional Formatting Picker – Example File

Prerequisites
Most options: select the cells(s) you want to apply formatting to.

Instructions
With cell(s) selected per the prerequisites above, run the Conditional Formatting Picker macro.  It will present several helpful conditional formatting options.  For the action you run most often, you can set your default so you don’t have to enter it (just launch the picker and press Enter).  Further instructions for the individual action codes are described below.

• [A] This will run the Add Banding macro, which highlights alternating rows to make them easier to read.  It is based on whether the contents of the first column in the selected range matches the row above, ignoring blanks – so keep this in mind when sizing rows, including blank rows.
• [V] This will run the Red Green CF Variance macro, which changes the font color to green for amounts greater than zero, and red for amounts less than zero.  It is best used with variance formulas – those that compare two or more numbers.
• [G] This will run the Good Bad Check Format macro, which applies a green or red fill color to selected cells.  It prompts you to enter a threshold (generally 0, or 1 to allow for rounding).  If the number is above the threshold, the cell is highlighted red, and if below the threshold, the cell is highlighted green.  It is best used for check figures when comparing items that should be equal or very close to equal.
• [R] This will run the CF Highlight Hard Coded macro, which will highlight any cells in the selection that are not blank and do not contain a formula.  This is very useful for formula auditing.
• [F] This will run the CF Highlight Hard Coded Formulas macro, which will highlight any cells in the selection containing formulas with hard-coded components.  Optionally, enter FF to also highlight hard-coded values (similar to option R).
• [L] This will run the CF Highlight Text Lengths macro, which will highlight cells with certain text lengths.  A prompt is displayed where you can enter a comparison operator (=, <>, >, >=, <, or <=), then the number of characters.
• [B] This will run the CF Highlight Blanks macro, which will highlight any blank cells.
• [U] This will highlight any cells with duplicate values.
• [H] This will run the CF Difference Highlight macro, which will compare two ranges and highlight any cells that contain different values.  It prompts you for the two ranges to compare, using the selected cells as a default.
• [E] This will run the CF Highlight Errors macro, which will highlight any cells containing formula errors as a result.
• [C] This will apply color scales as fill colors to the selected cells, based on their values relative to each other.
• [I] This will apply icons next to the selected cells, based on their values relative to each other.
• [D] This will apply data bars next to the selected cells, based on their values relative to each other.
• [T] This will apply formatting to the top/bottom values specified in the data set.  Optionally, you can add a space and then a negative number (-1 to -10) to highlight bottom values, or a space and then a positive number (1 to 10) to highlight top values.  For other values, use the conditional formatting rules manager.
• [O] This will run the Toggle Cell Tracker macro, which highlights the active cell, row, and column, and can track the active cell as you move.
• [P] This will run the Pause Conditional Formats macro, which can pause all conditional formatting rules on the active sheet or all sheets.  This is often useful for reviewing with the conditional formatting off, then turning it back on when finished drafting and reviewing.
• [N] This will run the Set Conditional Formatting Defaults macro, which lets you specify your preferred conditional formatting colors used in most of the options in this list.
• [X] This will clear all conditional formatting for the selected range within the selected sheet.
• [Z] This will clear all conditional formatting for the entire selected sheet.
• [K] This will run the Bulk Conditional Formats macro, where you can specify several different ranges, conditions, and formats and apply them in one bulk step, even reusing them over and over.
• [Q] This will display the conditional formatting rules manager, where you can apply or manage any type of conditional formatting settings.

Like most of the picker macros, you can set the default option to apply so you don’t even have to enter the letter/number – just the Enter key.  Just enter your preferred default letter(s), then click the default checkbox, and those letter(s) becomes the new default.