What it does
Uses a settings sheet (Bulk_Conditional_Formatting) to loop through a defined list of ranges to apply conditional formatting for.  The conditional formatting ranges are often in different sheets.

When to use it
When you want to quickly and easily apply specific conditional formatting to many different ranges/sheets within a workbook.  Example formats that are commonly applied are identifying exceptions, extreme values (low/high), blanks, and duplicates.

Why to use it
It’s an efficient and controlled way to apply conditional formatting to one or more sheets/ranges in a workbook.  Because conditional formatting can be a little tricky and time-consuming to get just right, this helps you leverage that effort over and over again to save time and frustration.

Default shortcut
None

Other Details

• Category: Formatting / Polish
• Difficulty: 5/5
• Usage/frequency: 2/5
• Automation factor: 5/5 (estimated 300 seconds saved each time used)
• Type: Bulk
• Date added: 1/8/2018
• Tags: conditional formatting, bulk, formats

Related Macros
Conditional Formatting Picker

Other Articles
None

Bulk Conditional Formats Example File

Instructions
After you have identified the sheets/ranges and formatting types to apply, run the Bulk Conditional Formats macro.  The first time you run it, it will create a sheet called Bulk_Conditional_Formats.  This is where you’ll configure all the sheets, ranges, and formats you want to apply.  These are the columns within the Bulk_Conditional_Formats sheet you’ll want to fill in:

• Column A – Sheet name (required): enter the sheet name containing the range you want to apply conditional formatting to.
• Column B – Range address/name (required) – enter the range address or range name that the conditional formatting should be applied to.
• Column C – Formula to evaluate (required) – enter the formula that should be used to evaluate the formatting condition(s), just as you would in the conditional formatting dialog box.  Text format has been applied so the formula doesn’t evaluate.
• Column D – Format to use (required): apply the exact formatting (i.e. yellow fill color) to use for the conditional format to the cells in this column.