Apply different data validation to column based on matching column first cell value

[matt767]

data validation = (list, decimal, whole number, etc.)

I have a sheet with a column for products (called variant.id, column F), followed by 20 blank attribute columns (H:AA), followed by their corresponding allowed values columns (AG:AZ), with the attribute name as column headers for both. The allowed values are either a vertical list or a single cell word like 'integer', 'inches', 'ounces', etc. I would like to use VBA to apply data validation list to the attribute columns for which the corresponding allowed values are a list, and data validation decimal (0 to 1000) to the attribute columns whose corresponding allowed values are 'inches', 'ounces', 'pounds', etc. (with input message 'Enter a number in inches, ounces, etc.'), and data validation whole number (0 to 100) to columns with allowed values 'integer' (with input message 'Enter a whole number').

In the VBA I would like to apply the data validation for the attribute columns stretching down to 50K rows and then clear cell contents for cells which return '#N/A' for a vlookup with a concatenation of variant.id column with column header against a pre-made column (column AD) of that combo in between the attribute columns and allowed values columns.

I realize this is a complicated request and I appreciate any assistance you can offer.

 

[matt767]

*down to 50K rows and then clear ALL for cells

 

[bebo021999]

Could you attach image/screenshot/XL2BB (https://www.mrexcel.com/board/excel-articles/xl2bb-excel-range-to-bbcode.1/)?

 

[matt767]

3 parts - the #VALUE! columns are just in case other sheets have more attributes, and in this case there are only 12 attributes:

Revman E.R. Int.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Date	Channel	Error Source	variant.name	SKU	variant.id	variant.product_id	fabric_weight_type	number_of_pieces	pattern_group	piece_1_length	piece_1_quantity	piece_1_type	piece_1_width	piece_2_quantity	piece_2_type	textile_construction	textile_dry_recommendation	textile_wash_recommendation
2	3/21/2022 14:22	target	integration	x	x	37802	x
3	3/18/2022 8:25	target	integration	x	x	33735	x
4	3/18/2022 8:25	target	integration	x	x	33730	x
5	3/17/2022 13:22	target	integration	x	x	35192	x
6	3/17/2022 13:22	target	integration	x	x	35197	x
7	3/17/2022 13:22	target	integration	x	x	27983	x
8	3/17/2022 13:22	target	integration	x	x	27986	x
9	3/17/2022 13:22	target	integration	x	x	35202	x
10	3/17/2022 13:22	target	integration	x	x	35207	x
11	3/17/2022 13:22	target	integration	x	x	35212	x
12	3/17/2022 13:22	target	integration	x	x	35217	x
13	3/17/2022 12:38	target	integration	x	x	37807	x
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
Bath Towel and Washcloth Sets

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H2:AA3,H5:AA5000,H4,J4:AA4	Expression	=VLOOKUP(CONCATENATE($F2,H$1),$AD:$AD,1,FALSE)=CONCATENATE($F2,H$1)	text	NO

Cells with Data Validation
Cell	Allow	Criteria
H2:H13	List	=$AG$2:$AG$5
I2:I3	Whole number	between 0 and 100
I5:I13	Whole number	between 0 and 100
J2:J13	List	=$AI$2:$AI$18
K2:K13	Whole number	between 0 and 1000

 

[matt767]

Revman E.R. Int.xlsm
	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL
1	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!	Is Fatal	Error Message		Attribute Path	Allowed Values:	fabric_weight_type	number_of_pieces	pattern_group	piece_1_length	piece_1_quantity	piece_1_type
2									1	Required no value: Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.Fabric Weight Type Required: Required Value Missing	37802fabric_weight_type	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.Fabric Weight Type		Heavyweight (550-800 GSM)	Value Type: xsd:integer	Abstract	Inches	Value Type: xsd:integer	Bath Sheet
3									1	Required no value: Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.Fabric Weight Type Required: Required Value Missing	33735fabric_weight_type	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.Fabric Weight Type		Lightweight (300-450 GSM)	Min values: 1	Animal Prints		Min values: 1	Bath Towel
4									1	Required no value: Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.Fabric Weight Type Required: Required Value Missing	33730fabric_weight_type	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.Fabric Weight Type		Midweight (450-550 GSM)	Max values: 1	Animals		Max values: 1	Hand Towel
5									1	Required no value: Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.Fabric Weight Type Required: Required Value Missing	35192fabric_weight_type	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.Fabric Weight Type		Year Round Fabric Construction	Documentation: Indicates how many items are included in the point of sale purchase of a single selling unit, usually indicates the total count of items in a set, but also clarifies to guest the item count for single items, such as chairs, pillowcases, or pillows. This attribute should be applied to different types of items that are sold together, as well as for one item that is sold in "sets of” or “packages of" two or more.	Botanical		Documentation: Indicates the quantity of the first piece in a set.	Hooded Bath Towel
6									1	Required no value: Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.Fabric Weight Type Required: Required Value Missing	35197fabric_weight_type	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.Fabric Weight Type				Classic Patterns			Washcloth
7									1	Required no value: Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.Fabric Weight Type Required: Required Value Missing	27983fabric_weight_type	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.Fabric Weight Type				Figures			Bath Mat
8									1	Required no value: Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.Fabric Weight Type Required: Required Value Missing	27986fabric_weight_type	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.Fabric Weight Type				Food			Fingertip Towel
9									1	Required no value: Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.Fabric Weight Type Required: Required Value Missing	35202fabric_weight_type	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.Fabric Weight Type				Geology			Robe
10									1	Required no value: Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.Fabric Weight Type Required: Required Value Missing	35207fabric_weight_type	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.Fabric Weight Type				Logo
11									1	Required no value: Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.Fabric Weight Type Required: Required Value Missing	35212fabric_weight_type	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.Fabric Weight Type				Print
12									1	Required no value: Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.Fabric Weight Type Required: Required Value Missing	35217fabric_weight_type	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.Fabric Weight Type				Shapes
13									1	Required no value: Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.Fabric Weight Type Required: Required Value Missing	37807fabric_weight_type	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.Fabric Weight Type				Solid
14									1	Required no value: Required Value Missing	37802number_of_pieces	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.number of pieces				Sports
15									1	Required no value: Required Value Missing	33735number_of_pieces	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.number of pieces				stripes
16									1	Required no value: Required Value Missing	33730number_of_pieces	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.number of pieces				Textile Designs
17									1	Required no value: Required Value Missing	35202number_of_pieces	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.number of pieces				Typography
18									1	Required no value: Required Value Missing	35207number_of_pieces	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.number of pieces				Vehicles
19									1	Required no value: Required Value Missing	35212number_of_pieces	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.number of pieces
20									1	Required no value: Required Value Missing	35217number_of_pieces	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.number of pieces
21									1	Required no value: Required Value Missing	37807number_of_pieces	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.number of pieces
22									1	Required no value: Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.Pattern Group Required: Required Value Missing	35192pattern_group	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.Pattern Group
23									1	Required no value: Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.Pattern Group Required: Required Value Missing	35197pattern_group	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.Pattern Group
24									1	Required no value: Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.Pattern Group Required: Required Value Missing	27983pattern_group	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.Pattern Group
25									1	Required no value: Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.Pattern Group Required: Required Value Missing	27986pattern_group	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.Pattern Group
26									1	Required no value: Required Value Missing	37802pattern_group	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.Pattern Group
27									1	Required no value: Required Value Missing	33735pattern_group	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.Pattern Group
28									1	Required no value: Required Value Missing	33730pattern_group	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.Pattern Group
29									1	Required no value: Required Value Missing	35202pattern_group	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.Pattern Group
30									1	Required no value: Required Value Missing	35207pattern_group	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.Pattern Group
31									1	Required no value: Required Value Missing	35212pattern_group	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.Pattern Group
32									1	Required no value: Required Value Missing	35217pattern_group	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.Pattern Group
33									1	Required no value: Required Value Missing	37807pattern_group	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.Pattern Group
34									1	Required no value	37802piece_1_length	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.piece 1 length
35									1	Required no value	33735piece_1_length	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.piece 1 length
36									1	Required no value	33730piece_1_length	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.piece 1 length
37									1	Required no value	35192piece_1_length	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.piece 1 length
38									1	Required no value	35197piece_1_length	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.piece 1 length
39									1	Required no value	27983piece_1_length	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.piece 1 length
40									1	Required no value	27986piece_1_length	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.piece 1 length
41									1	Required no value	35202piece_1_length	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.piece 1 length
42									1	Required no value	35207piece_1_length	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.piece 1 length
43									1	Required no value	35212piece_1_length	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.piece 1 length
44									1	Required no value	35217piece_1_length	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.piece 1 length
45									1	Required no value	37807piece_1_length	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.piece 1 length
46									1	Required no value: Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.piece 1 quantity Required: Required Value Missing	35192piece_1_quantity	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.piece 1 quantity
47									1	Required no value: Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.piece 1 quantity Required: Required Value Missing	35197piece_1_quantity	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.piece 1 quantity
48									1	Required no value: Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.piece 1 quantity Required: Required Value Missing	27983piece_1_quantity	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.piece 1 quantity
49									1	Required no value: Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.piece 1 quantity Required: Required Value Missing	27986piece_1_quantity	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.piece 1 quantity
50									1	Required no value: Required Value Missing	37802piece_1_quantity	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.piece 1 quantity
51									1	Required no value: Required Value Missing	33735piece_1_quantity	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.piece 1 quantity
52									1	Required no value: Required Value Missing	33730piece_1_quantity	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.piece 1 quantity
53									1	Required no value: Required Value Missing	35202piece_1_quantity	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.piece 1 quantity
54									1	Required no value: Required Value Missing	35207piece_1_quantity	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.piece 1 quantity
55									1	Required no value: Required Value Missing	35212piece_1_quantity	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.piece 1 quantity
56									1	Required no value: Required Value Missing	35217piece_1_quantity	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.piece 1 quantity
57									1	Required no value: Required Value Missing	37807piece_1_quantity	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.piece 1 quantity
58									1	Required no value: Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.piece 1 type Required: Required Value Missing	35192piece_1_type	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.piece 1 type
59									1	Required no value: Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.piece 1 type Required: Required Value Missing	35197piece_1_type	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.piece 1 type
60									1	Required no value: Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.piece 1 type Required: Required Value Missing	27983piece_1_type	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.piece 1 type
61									1	Required no value: Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.piece 1 type Required: Required Value Missing	27986piece_1_type	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.piece 1 type
62									1	Required no value: Required Value Missing	37802piece_1_type	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.piece 1 type
63									1	Required no value: Required Value Missing	33735piece_1_type	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.piece 1 type
64									1	Required no value: Required Value Missing	33730piece_1_type	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.piece 1 type
65									1	Required no value: Required Value Missing	35202piece_1_type	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.piece 1 type
66									1	Required no value: Required Value Missing	35207piece_1_type	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.piece 1 type
67									1	Required no value: Required Value Missing	35212piece_1_type	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.piece 1 type
68									1	Required no value: Required Value Missing	35217piece_1_type	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.piece 1 type
69									1	Required no value: Required Value Missing	37807piece_1_type	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.piece 1 type
70									1	Required no value	37802piece_1_width	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.piece 1 width
71									1	Required no value	33735piece_1_width	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.piece 1 width
72									1	Required no value	33730piece_1_width	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.piece 1 width
73									1	Required no value	35192piece_1_width	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.piece 1 width
74									1	Required no value	35197piece_1_width	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.piece 1 width
75									1	Required no value	27983piece_1_width	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.piece 1 width
76									1	Required no value	27986piece_1_width	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.piece 1 width
77									1	Required no value	35202piece_1_width	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.piece 1 width
78									1	Required no value	35207piece_1_width	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.piece 1 width
79									1	Required no value	35212piece_1_width	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.piece 1 width
80									1	Required no value	35217piece_1_width	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.piece 1 width
81									1	Required no value	37807piece_1_width	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.piece 1 width
82									1	Required no value: Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.piece 2 quantity Required: Required Value Missing	35192piece_2_quantity	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.piece 2 quantity
83									1	Required no value: Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.piece 2 quantity Required: Required Value Missing	35197piece_2_quantity	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.piece 2 quantity
84									1	Required no value: Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.piece 2 quantity Required: Required Value Missing	27983piece_2_quantity	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.piece 2 quantity
85									1	Required no value: Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.piece 2 quantity Required: Required Value Missing	27986piece_2_quantity	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.piece 2 quantity
86									1	Required no value: Required Value Missing	37802piece_2_quantity	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.piece 2 quantity
87									1	Required no value: Required Value Missing	33735piece_2_quantity	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.piece 2 quantity
88									1	Required no value: Required Value Missing	33730piece_2_quantity	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.piece 2 quantity
89									1	Required no value: Required Value Missing	35202piece_2_quantity	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.piece 2 quantity
90									1	Required no value: Required Value Missing	35207piece_2_quantity	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.piece 2 quantity
91									1	Required no value: Required Value Missing	35212piece_2_quantity	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.piece 2 quantity
92									1	Required no value: Required Value Missing	35217piece_2_quantity	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.piece 2 quantity
93									1	Required no value: Required Value Missing	37807piece_2_quantity	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.piece 2 quantity
94									1	Required no value: Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.piece 2 type Required: Required Value Missing	35192piece_2_type	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.piece 2 type
95									1	Required no value: Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.piece 2 type Required: Required Value Missing	35197piece_2_type	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.piece 2 type
96									1	Required no value: Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.piece 2 type Required: Required Value Missing	27983piece_2_type	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.piece 2 type
97									1	Required no value: Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.piece 2 type Required: Required Value Missing	27986piece_2_type	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.piece 2 type
98									1	Required no value: Required Value Missing	37802piece_2_type	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.piece 2 type
99									1	Required no value: Required Value Missing	33735piece_2_type	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.piece 2 type
100									1	Required no value: Required Value Missing	33730piece_2_type	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.piece 2 type
101									1	Required no value: Required Value Missing	35202piece_2_type	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.piece 2 type
102									1	Required no value: Required Value Missing	35207piece_2_type	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.piece 2 type
103									1	Required no value: Required Value Missing	35212piece_2_type	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.piece 2 type
104									1	Required no value: Required Value Missing	35217piece_2_type	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.piece 2 type
105									1	Required no value: Required Value Missing	37807piece_2_type	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.piece 2 type
106									1	Required no value: Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.textile construction Required: Required Value Missing	35192textile_construction	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.textile construction
107									1	Required no value: Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.textile construction Required: Required Value Missing	35197textile_construction	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.textile construction
108									1	Required no value: Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.textile construction Required: Required Value Missing	27983textile_construction	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.textile construction
109									1	Required no value: Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.textile construction Required: Required Value Missing	27986textile_construction	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.textile construction
110									1	Required no value: Required Value Missing	37802textile_construction	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.textile construction
111									1	Required no value: Required Value Missing	33735textile_construction	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.textile construction
112									1	Required no value: Required Value Missing	33730textile_construction	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.textile construction
113									1	Required no value: Required Value Missing	35202textile_construction	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.textile construction
114									1	Required no value: Required Value Missing	35207textile_construction	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.textile construction
115									1	Required no value: Required Value Missing	35212textile_construction	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.textile construction
116									1	Required no value: Required Value Missing	35217textile_construction	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.textile construction
117									1	Required no value: Required Value Missing	37807textile_construction	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.textile construction
118									1	Required no value: Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.Textile dry recommendation Required: Required Value Missing	35192textile_dry_recommendation	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.Textile dry recommendation
119									1	Required no value: Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.Textile dry recommendation Required: Required Value Missing	35197textile_dry_recommendation	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.Textile dry recommendation
120									1	Required no value: Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.Textile dry recommendation Required: Required Value Missing	27983textile_dry_recommendation	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.Textile dry recommendation
121									1	Required no value: Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.Textile dry recommendation Required: Required Value Missing	27986textile_dry_recommendation	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.Textile dry recommendation
122									1	Required no value: Required Value Missing	37802textile_dry_recommendation	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.Textile dry recommendation
123									1	Required no value: Required Value Missing	33735textile_dry_recommendation	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.Textile dry recommendation
124									1	Required no value: Required Value Missing	33730textile_dry_recommendation	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.Textile dry recommendation
125									1	Required no value: Required Value Missing	35202textile_dry_recommendation	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.Textile dry recommendation
126									1	Required no value: Required Value Missing	35207textile_dry_recommendation	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.Textile dry recommendation
127									1	Required no value: Required Value Missing	35212textile_dry_recommendation	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.Textile dry recommendation
128									1	Required no value: Required Value Missing	35217textile_dry_recommendation	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.Textile dry recommendation
129									1	Required no value: Required Value Missing	37807textile_dry_recommendation	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.Textile dry recommendation
130									1	Required no value: Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.Textile wash recommendation Required: Required Value Missing	35192textile_wash_recommendation	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.Textile wash recommendation
131									1	Required no value: Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.Textile wash recommendation Required: Required Value Missing	35197textile_wash_recommendation	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.Textile wash recommendation
132									1	Required no value: Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.Textile wash recommendation Required: Required Value Missing	27983textile_wash_recommendation	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.Textile wash recommendation
133									1	Required no value: Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.Textile wash recommendation Required: Required Value Missing	27986textile_wash_recommendation	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.Textile wash recommendation
134									1	Required no value: Required Value Missing	37802textile_wash_recommendation	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.Textile wash recommendation
135									1	Required no value: Required Value Missing	33735textile_wash_recommendation	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.Textile wash recommendation
136									1	Required no value: Required Value Missing	33730textile_wash_recommendation	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.Textile wash recommendation
137									1	Required no value: Required Value Missing	35202textile_wash_recommendation	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.Textile wash recommendation
138									1	Required no value: Required Value Missing	35207textile_wash_recommendation	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.Textile wash recommendation
139									1	Required no value: Required Value Missing	35212textile_wash_recommendation	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.Textile wash recommendation
140									1	Required no value: Required Value Missing	35217textile_wash_recommendation	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.Textile wash recommendation
141									1	Required no value: Required Value Missing	37807textile_wash_recommendation	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.Textile wash recommendation
Bath Towel and Washcloth Sets

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H2:AA3,H5:AA5000,H4,J4:AA4	Expression	=VLOOKUP(CONCATENATE($F2,H$1),$AD:$AD,1,FALSE)=CONCATENATE($F2,H$1)	text	NO

 

[matt767]

Revman E.R. Int.xlsm
	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ
1	piece_1_width	piece_2_quantity	piece_2_type	textile_construction	textile_dry_recommendation	textile_wash_recommendation	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!
2	Inches	Value Type: xsd:integer	Bath Mat	Canvas	Drying Instructions Not Provided	Do Not Wash
3		Min values: 1	Bath Sheet	Chenille	Line Dry or Lay Flat to Dry	Dry Clean
4		Max values: 1	Bath Towel	Chiffon	Tumble Dry	Hand Wash
5		Documentation: Indicates the quantity of the first piece in a set.	Fingertip Towel	Dobby		Spot or Wipe Clean
6			Hand Towel	Faux Suede		Washing Instructions Not Provided
7			Hooded Bath Towel	Flannel		Machine Wash
8			Robe	French Terry
9			Washcloth	Herringbone
10				Jacquard
11				Micro Plush
12				Microfiber
13				Muslin
14				Percale
15				Plain Weave
16				Plush
17				Slub
18				Terry
19				Twill
20				Velour
21				Waffle
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
Bath Towel and Washcloth Sets

 

[matt767]

Just ignore the data validation already in the first part...forgot to delete that.

 

[bebo021999]

Not hundred percent understanding you, but try:

Option Explicit
Sub TEST()
Dim lr&, cell As Range, f, rng
lr = Cells(Rows.Count, "A").End(xlUp).Row
'1) set validation for column I,L,O with integer
    With Union(Range("I2:I" & lr), Range("L2:L" & lr), Range("O2:O" & lr)).Validation
        .Delete
        .Add xlValidateWholeNumber, xlValidAlertStop, xlBetween, 5, 10
        .InputTitle = "enter a whole number"
        .ErrorTitle = "Invalid input"
        .InputMessage = "Enter an integer from 0 to 100"
        .ErrorMessage = "You must enter a number from 0 to 100"
    End With
'2) set validation for column K,N with inches
     With Union(Range(" K2:K" & lr), Range("N2:N" & lr)).Validation
        .Delete
        .Add xlValidateWholeNumber, xlValidAlertStop, xlBetween, 0, 1000
        .InputTitle = "enter inches"
        .ErrorTitle = "Invalid input"
        .InputMessage = "Enter inch from 0 to 1000"
        .ErrorMessage = "You must enter a number from 0 to 1000"
    End With
'3) set validation for others columns with list
     For Each cell In Union(Range("H1"), Range("J1"), Range("M1"), Range("P1:S1"))
        Set f = Range("AG1:AZ1").Find(cell.Value) ' search the header accross
            If Not f Is Nothing Then
              Set rng = Range(f.Offset(1, 0), Cells(Rows.Count, f.Column).End(xlUp)) ' if found, run down to to get validation list
                With Range(cell.Offset(1, 0), Cells(Rows.Count, cell.Column).End(xlUp)).Validation
                    .Delete
                    .Add xlValidateList, , , "=" & rng.Address
                    .InputMessage = "pick an item"
                End With
            End If
        Next
End Sub

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR
1	Date	Channel	Error Source	variant.name	SKU	variant.id	variant.product_id	fabric_weight_type	number_of_pieces	pattern_group	piece_1_length	piece_1_quantity	piece_1_type	piece_1_width	piece_2_quantity	piece_2_type	textile_construction	textile_dry_recommendation	textile_wash_recommendation	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!	Is Fatal	Error Message		Attribute Path	Allowed Values:	fabric_weight_type	number_of_pieces	pattern_group	piece_1_length	piece_1_quantity	piece_1_type	piece_1_width	piece_2_quantity	piece_2_type	textile_construction	textile_dry_recommendation	textile_wash_recommendation
2	21/03/2022 14:22	target	integration	x	x	37802	x																					1	Required no value: Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.Fabric Weight Type Required: Required Value Missing	37802fabric_weight_type	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.Fabric Weight Type		Heavyweight (550-800 GSM)	Value Type: xsd:integer	Abstract	Inches	Value Type: xsd:integer	Bath Sheet	Inches	Value Type: xsd:integer	Bath Mat	Canvas	Drying Instructions Not Provided	Do Not Wash
3	18/03/2022 08:25	target	integration	x	x	33735	x																					1	Required no value: Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.Fabric Weight Type Required: Required Value Missing	33735fabric_weight_type	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.Fabric Weight Type		Lightweight (300-450 GSM)	Min values: 1	Animal Prints		Min values: 1	Bath Towel		Min values: 1	Bath Sheet	Chenille	Line Dry or Lay Flat to Dry	Dry Clean
4	18/03/2022 08:25	target	integration	x	x	33730	x																					1	Required no value: Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.Fabric Weight Type Required: Required Value Missing	33730fabric_weight_type	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.Fabric Weight Type		Midweight (450-550 GSM)	Max values: 1	Animals		Max values: 1	Hand Towel		Max values: 1	Bath Towel	Chiffon	Tumble Dry	Hand Wash
5	17/03/2022 13:22	target	integration	x	x	35192	x																					1	Required no value: Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.Fabric Weight Type Required: Required Value Missing	35192fabric_weight_type	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.Fabric Weight Type		Year Round Fabric Construction	Documentation: Indicates how many items are included in the point of sale purchase of a single selling unit, usually indicates the total count of items in a set, but also clarifies to guest the item count for single items, such as chairs, pillowcases, or pillows. This attribute should be applied to different types of items that are sold together, as well as for one item that is sold in "sets of” or “packages of" two or more.	Botanical		Documentation: Indicates the quantity of the first piece in a set.	Hooded Bath Towel		Documentation: Indicates the quantity of the first piece in a set.	Fingertip Towel	Dobby		Spot or Wipe Clean
6	17/03/2022 13:22	target	integration	x	x	35197	x																					1	Required no value: Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.Fabric Weight Type Required: Required Value Missing	35197fabric_weight_type	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.Fabric Weight Type				Classic Patterns			Washcloth			Hand Towel	Faux Suede		Washing Instructions Not Provided
7	17/03/2022 13:22	target	integration	x	x	27983	x																					1	Required no value: Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.Fabric Weight Type Required: Required Value Missing	27983fabric_weight_type	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.Fabric Weight Type				Figures			Bath Mat			Hooded Bath Towel	Flannel		Machine Wash
8	17/03/2022 13:22	target	integration	x	x	27986	x																					1	Required no value: Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.Fabric Weight Type Required: Required Value Missing	27986fabric_weight_type	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.Fabric Weight Type				Food			Fingertip Towel			Robe	French Terry
9	17/03/2022 13:22	target	integration	x	x	35202	x																					1	Required no value: Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.Fabric Weight Type Required: Required Value Missing	35202fabric_weight_type	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.Fabric Weight Type				Geology			Robe			Washcloth	Herringbone
10	17/03/2022 13:22	target	integration	x	x	35207	x																					1	Required no value: Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.Fabric Weight Type Required: Required Value Missing	35207fabric_weight_type	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.Fabric Weight Type				Logo							Jacquard
11	17/03/2022 13:22	target	integration	x	x	35212	x																					1	Required no value: Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.Fabric Weight Type Required: Required Value Missing	35212fabric_weight_type	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.Fabric Weight Type				Print							Micro Plush
12	17/03/2022 13:22	target	integration	x	x	35217	x																					1	Required no value: Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.Fabric Weight Type Required: Required Value Missing	35217fabric_weight_type	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.Fabric Weight Type				Shapes							Microfiber
13	17/03/2022 12:38	target	integration	x	x	37807	x																					1	Required no value: Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.Fabric Weight Type Required: Required Value Missing	37807fabric_weight_type	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.Fabric Weight Type				Solid							Muslin
14																												1	Required no value: Required Value Missing	37802number_of_pieces	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.number of pieces				Sports							Percale
15																												1	Required no value: Required Value Missing	33735number_of_pieces	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.number of pieces				stripes							Plain Weave
16																												1	Required no value: Required Value Missing	33730number_of_pieces	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.number of pieces				Textile Designs							Plush
17																												1	Required no value: Required Value Missing	35202number_of_pieces	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.number of pieces				Typography							Slub
18																												1	Required no value: Required Value Missing	35207number_of_pieces	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.number of pieces				Vehicles							Terry
19																												1	Required no value: Required Value Missing	35212number_of_pieces	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.number of pieces											Twill
20																												1	Required no value: Required Value Missing	35217number_of_pieces	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.number of pieces											Velour
21																												1	Required no value: Required Value Missing	37807number_of_pieces	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.number of pieces											Waffle
22																												1	Required no value: Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.Pattern Group Required: Required Value Missing	35192pattern_group	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.Pattern Group
23																												1	Required no value: Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.Pattern Group Required: Required Value Missing	35197pattern_group	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.Pattern Group
24																												1	Required no value: Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.Pattern Group Required: Required Value Missing	27983pattern_group	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.Pattern Group
25																												1	Required no value: Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.Pattern Group Required: Required Value Missing	27986pattern_group	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.Pattern Group
26																												1	Required no value: Required Value Missing	37802pattern_group	Target Products.HOME.BATH.Bath Towels.Bath Towel and Washcloth Sets.Pattern Group
Sheet4

Cells with Data Validation
Cell	Allow	Criteria
I2:I13	Whole number	between 5 and 10
K2:K13	Whole number	between 0 and 1000
L2:L13	Whole number	between 5 and 10
N2:N13	Whole number	between 0 and 1000
O2:O13	Whole number	between 5 and 10
H2:H26	List	=$AG$2:$AG$5
J2:J26	List	=$AI$2:$AI$18
M2:M26	List	=$AL$2:$AL$9
P2:P180	List	=$AO$2:$AO$9
Q2:Q180	List	=$AP$2:$AP$21
R2:R180	List	=$AQ$2:$AQ$4
S2:S180	List	=$AR$2:$AR$7

 

[matt767]

I found a way to do the validation fill piece by piece:

Sub AddVal()
Dim SrchRng As Range
Dim cel As Range
Set SrchRng = Range("ag2:az2")
For Each cel In SrchRng
If InStr(1, cel.Value, "Inches") > 0 Then
Range(cel.Offset(0, -25), cel.Offset(50, -25)).Select
With Selection.Validation
.Delete
.Add Type:=xlValidateDecimal, AlertStyle:=xlValidAlertStop, Operator _
:=xlBetween, Formula1:="0", Formula2:="1000"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = "Enter a number in inches."
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End If
Next cel
End Sub

Sub AddVal2()
Dim ws As Worksheet
Dim SrchRng As Range
Dim cel As Range
Dim list As Range
Dim list2 As Range
Dim num As Range
Set ws = ActiveSheet
Set SrchRng = Range("ag2:az2")
Set num = Range("ba1")
For Each cel In SrchRng
Set list = Range(cel.Offset(0, 0), cel.Offset(50, 0))
num = WorksheetFunction.CountA(list)
Set list2 = Range(cel.Offset(0, 0), cel.Offset(num, 0))
If InStr(1, cel.Value, "Inches") = 0 And InStr(1, cel.Value, "integer") = 0 Then
Range(cel.Offset(0, -25), cel.Offset(50, -25)).Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="='" & ws.Name & "'!" & list2.Address
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End If
Next cel
End Sub

I will combine these macros into one and add one for 'integer' to get all I need for this sheet. All that's left is clearing all from the cells that are not needed, e.g. those that return '#N/A' from a vlookup concatenating column F and column header against column AD.

 

[matt767]

Thank you @bebo021999. I also found my own solution above. Is there a VBA to clear all from the cells that are not needed, e.g. those that return '#N/A' from a vlookup concatenating column F and column header against column AD? In your solution that would clear cells I4:I8.