What it does
Uses a settings sheet to update formats across multiple cells in one bulk step. It can update formats across multiple files, then save and close those files after updating. This makes it easy to make bulk updates and reuse those efforts.  Note that this will apply fixed formats in bulk.  To apply conditional formats in bulk, see the Bulk Conditional Formats macro page.

When to use it
When you want to make several formatting changes across multiple cells and even multiple files in one bulk step, this is a quick way to do it.  The formats can also be reapplied over and over again.

Why to use it
It’s a quick and easy way to update formats across multiple cells and even multiple files in one bulk step.

Default shortcut
None

Other Details

• Category: Formatting / General
• Difficulty: 4/5
• Usage/frequency: 2/5
• Automation factor: 5/5 (estimated 300 seconds saved each time used)
• Type: Bulk
• Date added: 2/11/2024
• Tags: Formats, bulk, highlight, font, numbers

Related Macros
Bulk Conditional Formats

Other Articles
None

Bulk Format Cells – Example File

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

• Column A – File path (optional): to update formatting in files other than the active workbook where the settings sheet is in, enter the entire file path.
• Column B – Sheet name (required) – enter the name of the sheet containing the range to format.
• Column C – Range address/name (required) – enter the range address or range name that the formatting should be applied to. To update multiple ranges with the same formatting, separate the ranges with a comma (i.e. A1:A3,D1:D3,A21,D21).
• Column D – Format type (required) – select the format type to update from the drop-down list (click the arrow or Alt+Down arrow to show the full list). To select multiple options, click Shift+Alt+Down Arrow to show the data validation search box. Formatting options available are: all, number format, fill color, font color, font type, font style, font size, border, and alignment.
• Column E – Format value (required): apply the exact formatting (i.e. yellow fill color) you want to apply.
• Column F – Save/close (optional): if updating multiple files, use the options here to save them after updating, or save and close after updating.

None