What it does
Uses a settings sheet (Bulk_Data_Validation) to loop through a defined list of data validation settings to apply across specified sheets and ranges.  All data validation types are supported, including “Any value” (no data validation applied) and the ability to show/hide input messages and error messages and their contents.

When to use it
When you need to apply data validation across a number of sheets and/or ranges within your workbook, whether or not the data validation settings are consistent, this makes it quick, easy, and controlled.  This is often the case when collecting department-level or location-level information.

Why to use it
It’s an efficient and controlled way to apply data validation in bulk across many sheets/ranges, offering two options: 1) manually apply the data validation and it will be copied or 2) list out settings for specific data validation attributes.  The settings sheet can also be leveraged over and over again, saving even more time.

Default shortcut
None

Other Details

• Category: Data / Content
• Difficulty: 5/5
• Usage/frequency: 2/5
• Automation factor: 5/5 (estimated 300 seconds saved each time used)
• Type: Bulk
• Date added: 4/26/2020
• Tags: Data validation, bulk, values

Related Macros
Data Validation Picker
Search Data Validation List
Add Data Validation From Named Range

Other Articles
None

Bulk Data Validation Example File

Prerequisites
Identify the sheet(s) and range(s) you want to apply data validation to.

Instructions
After you have identified the sheet(s) and range(s) you want to apply data validation to, run the Bulk Data Validation macro.  The first time you run it, it will create a sheet called Bulk_Data_Validation.  This is where you’ll configure all the data validation settings to apply, and the sheet(s)/range(s) the settings will be applied to.  Note that there are two options for specifying the data validation: 1) manually apply it in the settings sheet (column C) and it will be copied to the specified sheet/range, or 2) list the different validation attributes (columns D:M).  These are the columns within the Bulk_Data_Validation sheet you’ll want to fill in:

• Column A – Sheet name (required): enter the sheet name containing the range where you want to apply data validation.
• Column B – Apply to range/address/name (required) – enter the range address or range name within the sheet name above where you want to apply data validation.
• Column C – Data validation to apply (required for option 1) – set the data validation settings to the cell for each sheet/range.  No value is required.  If this option is used, it will override all the settings in columns D:M.
• Column D – Validation Type (required for option 2): select the validation type from the drop-down list.
• Column E – Value 1 (required for option 2 – most types): for whole number, decimal, date, time, or text length types, specify the first value to compare to using the operator type in column G.  For the list type, list out the values to use separated by a comma OR enter the = symbol and the range address/name with the list to use.
• Column F – Value 2 (required for option 2 depending on type/operator): for whole number, decimal, date, time, or text length types, specify the second value to compare against, if needed for between/not between operators.
• Column G – Value operator (required for option 2 – most types): for whole number, decimal, date, time, or text length types, specify the operator type that should be used with value 1 in column E and value 2 in column F (if used).
• Column H – Input message show (optional): enter Show to show the input message when the cell containing data validation is selected.
• Column I – Input message title (optional): enter a title for the input message to display when the cell containing data validation is selected.
• Column J – Input message text (optional): enter the text for the input message to display when the cell containing data validation is selected.
• Column K – Error message show (optional): enter Show to show the error message when an invalid value is entered into the cell containing data validation.
• Column L – Error message title (optional): enter a title for the error message when an invalid value is entered into the cell containing data validation.
• Column M – Error message text (optional): enter the text for the error message when an invalid value is entered into the cell containing data validation.

TBD…