Copying formatting from one sheet to another, based on cell contents

[Ian1976]

Hi,
I have a worksheet "Fault List" with a list of fault codes - Column A - 7231, 4563 & 8634 (approx 300 fault codes) these cells are all red, yellow or blue. next to the fault codes is a summary of the fault (Column B)
2340 may be blue
7123 may be red

When I enter a fault code on my main sheet in cell K10 ( for example 2340, which is Blue) there is a lookup which then pulls the fault summary and puts it in cell K11, what I'd like to know is when I enter a fault code on the main worksheet can I get it to "fetch" the colour of the cell from the "Fault List" too?

The below images may help, as I may not have explained myself correctly?

The coloured fault list is on sheet 4, the cell K10 on the second image is the one I manually change, is there a way to make it's cell colour match the one in the fault list, i.e. Blue?

Thanks

 

[Ian1976]

And with 300+ fault code it's difficult to find a pattern... Difficult but achievable and XL2BB does not allow to copy paste that big data...

Yes I was hoping that the solution would allow me to just alter it to suit the 300 other numbers!

 

[SanjayGMusafir]

lol, unfortunately there is no pattern to the colours/numbers, that's how they came generated!

You know we can populate the rule(s) manually based on values and Using AND function in Conditional formatting. Time taking but can be done if that is extremely important.

VBA I'm a novice so I don't want to give you a wrong code - That's why I told you to keep thread unresolved.

 

[Ian1976]

You know we can populate the rule(s) manually based on values and Using AND function in Conditional formatting. Time taking but can be done if that is extremely important.

VBA I'm a novice so I don't want to give you a wrong code - That's why I told you to keep thread unresolved.

It would be very useful to me to "have" it as it's a visual aid to the severity of the fault code!

 

[SanjayGMusafir]

It would be very useful to me to "have" it as it's a visual aid to the severity of the fault code!

Luckily I'm free right now

You paste all 300+ rows here using XL2BB - Probably it allows 50 rows at a time

You copy only that column as mini sheet and paste here in 6+ attempts - I see if I can help you in some way - Would try not commit - Although I'm a person if I get into something try to resolve it

 

[Ian1976]

Luckily I'm free right now

You paste all 300+ rows here using XL2BB - Probably it allows 50 rows at a time

You copy only that column as mini sheet and paste here in 6+ attempts - I see if I can help you in some way - Would try not commit - Although I'm a person if I get into something try to resolve it

its let me do them all?

160922_Days.xlsm
	I
1
2	7001
3	7002
4	7003
5	7004
6	7005
7	7006
8	7007
9	7009
10	7011
11	7012
12	7014
13	7015
14	7016
15	7017
16	7018
17	7019
18	7020
19	7021
20	7022
21	7023
22	7026
23	7027
24	7028
25	7029
26	7030
27	7031
28	7032
29	7033
30	7034
31	7035
32	7036
33	7037
34	7038
35	7039
36	7040
37	7041
38	7042
39	7043
40	7044
41	7045
42	7046
43	7047
44	7048
45	7050
46	7051
47	7052
48	7053
49	7054
50	7055
51	7056
52	7057
53	7058
54	7059
55	7061
56	7062
57	7063
58	7064
59	7066
60	7067
61	7068
62	7069
63	7070
64	7071
65	7072
66	7073
67	7074
68	7076
69	7077
70	7078
71	7079
72	7080
73	7081
74	7082
75	7083
76	7084
77	7085
78	7086
79	7087
80	7088
81	7089
82	7090
83	7091
84	7092
85	7093
86	7094
87	7095
88	7096
89	7097
90	7098
91	7099
92	7100
93	7101
94	7102
95	7103
96	7104
97	7105
98	7106
99	7107
100	7108
101	7109
102	7110
103	7111
104	7112
105	7113
106	7114
107	7115
108	7116
109	7117
110	7118
111	7119
112	7120
113	7121
114	7122
115	7123
116	7124
117	7125
118	7126
119	7127
120	7128
121	7129
122	7130
123	7131
124	7132
125	7133
126	7134
127	7135
128	7136
129	7137
130	7138
131	7139
132	7140
133	7141
134	7144
135	7145
136	7146
137	7147
138	7148
139	7150
140	7152
141	7153
142	7154
143	7155
144	7156
145	7157
146	7159
147	7160
148	7161
149	7162
150	7163
151	7164
152	7165
153	7166
154	7167
155	7168
156	7169
157	7170
158	7171
159	7172
160	7173
161	7174
162	7175
163	7176
164	7177
165	7178
166	7179
167	7180
168	7181
169	7182
170	7183
171	7184
172	7185
173	7186
174	7187
175	7188
176	7189
177	7190
178	7191
179	7192
180	7193
181	7194
182	7196
183	7197
184	7198
185	7199
186	7200
187	7201
188	7202
189	7203
190	7205
191	7206
192	7207
193	7208
194	7209
195	7210
196	7211
197	7212
198	7213
199	7214
200	7215
201	7217
202	7218
203	7219
204	7220
205	7221
206	7222
207	7223
208	7224
209	7225
210	7226
211	7227
212	7228
213	7229
214	7230
215	7231
216	7238
217	7239
218	7240
219	7241
220	7242
221	7243
222	7244
223	7245
224	7246
225	7249
226	7252
227	7253
228	7254
229	7255
230	7256
231	7257
232	7258
233	7259
234	7260
235	7261
236	7262
237	7263
238	7264
239	7265
240	7266
241	7267
242	7268
243	7269
244	7270
245	7271
246	7272
247	7273
248	7274
249	7275
250	7276
251	7277
252	7278
253	7279
254	7280
255	7284
256	7285
257	7286
258	7287
259	7288
260	7289
261	7291
262	7292
263	7293
264	7294
265	7295
266	7296
267	7297
268	7298
269	7299
270	7300
271	7301
272	7302
273	7303
274	7304
275	7305
276	7306
277	7307
278	7308
279	7309
280	7312
281	7323
282	7330
283	7331
284	7332
285	7333
286	7334
287	7335
288	7337
289	7338
290	7339
291	7340
292	7341
293	7342
294	7343
295	7344
296	7345
297	7346
298	7347
299	7348
300	7349
301	7350
302	7351
303	7352
304	7353
305	7354
306	7355
307	7356
308	7357
309	7358
310	7361
311	7362
312	7363
313	7364
314	7365
315	7372
316	7373
317	7380
318	7390
319	7391
320	7392
321	7393
322	7394
323	7395
324	7396
325	7397
326	7398
327	7399
328	7400
329	7401
330	7402
331	7403
332	7404
333	7405
334	7406
335	7407
336	7420
337	7421
338	7422
339	7423
340	7424
341	7425
342	7426
343	7427
344	7428
345	7429
346	7430
347	7431
348	7432
349	7433
350	7434
351	7435
352	7436
353	7437
354	7438
355	7439
356	7440
357	7441
358	7501
359	7502
360	7503
361	7508
362	7509
363	7510
364	7511
365	7374
366	7375
367	7376
368	7377
369	7378
370	7379
Sheet4

 

[SanjayGMusafir]

Here underneath I'm giving you an idea through 3 XL2BB attachments (in this thread only - Just copy from utility and paste one after another in thread) how you can achieve the results - As you said it was important for you!

In cell O2 I have tested the rules we have created. And in Cell N3, I have mentioned the way to copy all the conditional formatting from one sheet to another in one single step. See the attached screenshot too. And it's done.

I have given you the idea, now you have to the efforts for yourselves.

All Records.xlsb
	I	J
1	Original	Tested
2	7002	7002
3	7003	7003
4	7004	7004
5	7005	7005
6	7006	7006
7	7007	7007
8	7015	7015
9	7016	7016
10	7017	7017
11	7019	7019
12	7029	7029
13	7030	7030
14	7031	7031
15	7034	7034
16	7035	7035
17	7038	7038
18	7039	7039
19	7040	7040
20	7041	7041
21	7042	7042
22	7043	7043
23	7047	7047
24	7048	7048
25	7050	7050
26	7051	7051
27	7052	7052
28	7053	7053
29	7054	7054
30	7055	7055
31	7056	7056
32	7058	7058
33	7059	7059
34	7061	7061
35	7062	7062
36	7063	7063
37	7064	7064
38	7066	7066
39	7067	7067
40	7068	7068
41	7069	7069
42	7070	7070
43	7073	7073
44	7074	7074
45	7076	7076
46	7079	7079
47	7080	7080
48	7081	7081
49	7082	7082
50	7083	7083
51	7084	7084
52	7085	7085
53	7086	7086
54	7087	7087
55	7088	7088
56	7093	7093
57	7094	7094
58	7095	7095
59	7096	7096
60	7097	7097
61	7098	7098
62	7099	7099
63	7100	7100
64	7105	7105
65	7106	7106
66	7107	7107
67	7108	7108
68	7109	7109
69	7110	7110
70	7111	7111
71	7112	7112
72	7117	7117
73	7118	7118
74	7119	7119
75	7120	7120
76	7121	7121
77	7122	7122
78	7123	7123
79	7124	7124
80	7129	7129
81	7130	7130
82	7131	7131
83	7132	7132
84	7135	7135
85	7136	7136
86	7137	7137
87	7138	7138
88	7141	7141
89	7145	7145
90	7146	7146
91	7147	7147
92	7148	7148
93	7152	7152
94	7153	7153
95	7154	7154
96	7155	7155
97	7156	7156
98	7157	7157
99	7169	7169
100	7170	7170
101	7171	7171
102	7172	7172
103	7175	7175
104	7184	7184
105	7185	7185
106	7186	7186
107	7188	7188
108	7189	7189
109	7190	7190
110	7196	7196
111	7197	7197
112	7199	7199
113	7203	7203
114	7205	7205
115	7206	7206
116	7212	7212
117	7213	7213
118	7214	7214
119	7215	7215
120	7217	7217
121	7218	7218
122	7219	7219
123	7220	7220
124	7223	7223
125	7224	7224
126	7225	7225
127	7226	7226
128	7229	7229
129	7230	7230
130	7231	7231
131	7238	7238
132	7239	7239
133	7240	7240
134	7241	7241
135	7242	7242
136	7243	7243
137	7244	7244
138	7245	7245
139	7288	7288
140	7289	7289
141	7333	7333
142	7334	7334
143	7335	7335
144	7345	7345
145	7347	7347
146	7348	7348
147	7349	7349
148	7354	7354
149	7355	7355
150	7356	7356
151	7357	7357
152	7361	7361
153	7362	7362
154	7363	7363
155	7364	7364
156	7372	7372
157	7373	7373
158	7380	7380
159	7390	7390
160	7391	7391
161	7392	7392
162	7393	7393
163	7420	7420
164	7502	7502
165	7503	7503
166	7376	7376
167	7377	7377
168	7378	7378
169	7009	7009
170	7012	7012
171	7020	7020
172	7021	7021
173	7028	7028
174	7036	7036
175	7037	7037
176	7044	7044
177	7046	7046
178	7089	7089
179	7090	7090
180	7091	7091
181	7092	7092
182	7101	7101
183	7102	7102
184	7103	7103
185	7104	7104
186	7113	7113
187	7114	7114
188	7115	7115
189	7116	7116
190	7125	7125
191	7126	7126
192	7127	7127
193	7128	7128
194	7133	7133
195	7134	7134
196	7139	7139
197	7140	7140
198	7150	7150
199	7165	7165
200	7166	7166
201	7167	7167
202	7168	7168
203	7173	7173
204	7174	7174
205	7176	7176
206	7177	7177
207	7178	7178
208	7179	7179
209	7180	7180
210	7181	7181
211	7182	7182
212	7183	7183
213	7187	7187
214	7198	7198
215	7200	7200
216	7201	7201
217	7202	7202
218	7207	7207
219	7208	7208
220	7209	7209
221	7210	7210
222	7211	7211
223	7221	7221
224	7222	7222
225	7227	7227
226	7228	7228
227	7258	7258
228	7259	7259
229	7260	7260
230	7263	7263
231	7264	7264
232	7265	7265
233	7268	7268
234	7269	7269
235	7270	7270
236	7273	7273
237	7274	7274
238	7275	7275
239	7276	7276
240	7279	7279
241	7280	7280
242	7284	7284
243	7285	7285
244	7337	7337
245	7338	7338
246	7339	7339
247	7340	7340
248	7341	7341
249	7358	7358
250	7401	7401
251	7402	7402
252	7403	7403
253	7404	7404
254	7405	7405
255	7406	7406
256	7407	7407
257	7421	7421
258	7422	7422
259	7423	7423
260	7424	7424
261	7501	7501
262	7508	7508
263	7509	7509
264	7510	7510
265	7511	7511
266	7374	7374
267	7375	7375
268	7379	7379
269	7001	7001
270	7011	7011
271	7014	7014
272	7018	7018
273	7022	7022
274	7023	7023
275	7026	7026
276	7027	7027
277	7032	7032
278	7033	7033
279	7045	7045
280	7057	7057
281	7071	7071
282	7072	7072
283	7077	7077
284	7078	7078
285	7144	7144
286	7159	7159
287	7160	7160
288	7161	7161
289	7162	7162
290	7163	7163
291	7164	7164
292	7191	7191
293	7192	7192
294	7193	7193
295	7194	7194
296	7246	7246
297	7249	7249
298	7252	7252
299	7253	7253
300	7254	7254
301	7255	7255
302	7256	7256
303	7257	7257
304	7261	7261
305	7262	7262
306	7266	7266
307	7267	7267
308	7271	7271
309	7272	7272
310	7277	7277
311	7278	7278
312	7286	7286
313	7287	7287
314	7291	7291
315	7292	7292
316	7293	7293
317	7294	7294
318	7295	7295
319	7296	7296
320	7297	7297
321	7298	7298
322	7299	7299
323	7300	7300
324	7301	7301
325	7302	7302
326	7303	7303
327	7304	7304
328	7305	7305
329	7306	7306
330	7307	7307
331	7308	7308
332	7309	7309
333	7312	7312
334	7323	7323
335	7330	7330
336	7331	7331
337	7332	7332
338	7342	7342
339	7343	7343
340	7344	7344
341	7346	7346
342	7350	7350
343	7351	7351
344	7352	7352
345	7353	7353
346	7365	7365
347	7394	7394
348	7395	7395
349	7396	7396
350	7397	7397
351	7398	7398
352	7399	7399
353	7400	7400
354	7425	7425
355	7426	7426
356	7427	7427
357	7428	7428
358	7429	7429
359	7430	7430
360	7431	7431
361	7432	7432
362	7433	7433
363	7434	7434
364	7435	7435
365	7436	7436
366	7437	7437
367	7438	7438
368	7439	7439
369	7440	7440
370	7441	7441
Sheet3

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
J2:J370	Expression	=K2<>""	text	NO

Its not allowing to copy the whole columns since it contains formula or formatting so copying a small part for your understanding -

All Records.xlsb
	K
1	Test
2
3
4
5
6
7
8	8
9
10
11	2
12	10
13
14
15	3
16
17	3
18
19
20
21
22
23	4
Sheet3

Cell Formulas
Range	Formula
K2:K23	K2	=IFERROR(IF([@Tested]-OFFSET([@Tested],-1,0)>1,[@Tested]-OFFSET([@Tested],-1,0),""),"")

Its not allowing to copy the whole columns since it contains formula or formatting so copying a small part for your understanding -

All Records.xlsb
	L
1	Formatted
2	7002
3	7003
4	7004
5	7005
6	7006
7	7007
8	7015
9	7016
10	7017
11	7019
12	7029
13	7030
14	7031
15	7034
16	7035
17	7038
18	7039
19	7040
20	7041
21	7042
22	7043
Sheet3

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
L2:L370	Cell Value	between 7176 and 7183	text	NO
L2:L370	Cell Value	between 7159 and 7164	text	NO
L2:L370	Cell Value	between 7079 and 7088	text	NO
L2:L370	Cell Value	between 7066 and 7070	text	NO
L2:L370	Cell Value	between 7061 and 7064	text	NO
L2:L370	Cell Value	between 7050 and 7056	text	NO
L2:L370	Cell Value	between 7038 and 7043	text	NO
L2:L370	Cell Value	between 7029 and 7031	text	NO
L2:L370	Cell Value	between 7002 and 7007	text	NO

Its not allowing to copy the whole columns since it contains formula or formatting so copying a small part for your understanding -

All Records.xlsb
	N	O
1
2	Test the rule	7177
3	Simply copy cell L2 and paste Formatting using paste special in Cell O2 And its done!
4
5
6
7
Sheet3

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
O2	Cell Value	between 7176 and 7183	text	NO
O2	Cell Value	between 7159 and 7164	text	NO
O2	Cell Value	between 7079 and 7088	text	NO
O2	Cell Value	between 7066 and 7070	text	NO
O2	Cell Value	between 7061 and 7064	text	NO
O2	Cell Value	between 7050 and 7056	text	NO
O2	Cell Value	between 7038 and 7043	text	NO
O2	Cell Value	between 7029 and 7031	text	NO
O2	Cell Value	between 7002 and 7007	text	NO

 

[Ian1976]

Thank you Sanjay, I'll give it a go!

 

[SanjayGMusafir]

Thank you Sanjay, I'll give it a go!

Once done, you can mark the thread that actually answered your query as solution.

Best wishes