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

 

[SanjayGMusafir]

I'm somewhat clear what you want. But I can't regenerate whole data to explain what needs to be done.

So you upload sample data of all sheets using XL2BB utility (You will get the link in my signatures below)

Then I will copy your sample data and revert with a solution - Quick and efficient way

 

[Ian1976]

Example1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
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
Sheet4

 

[Ian1976]

Example1.xlsx
	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
1
2
3
4
5
6
7
8
9
10											7026
11
12
13
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
Sheet2

Cell Formulas
Range	Formula
Q11	Q11	=IFNA(VLOOKUP(Q10,[160922_Days.xlsm]Sheet4!I2:L360,2,FALSE),"")
W11	W11	=IFNA(VLOOKUP(W10,[160922_Days.xlsm]Sheet4!I2:L360,2,FALSE),"")
Q12	Q12	=IFNA(VLOOKUP(Q10,[160922_Days.xlsm]Sheet4!I2:L360,4,FALSE),"")
W12	W12	=IFNA(VLOOKUP(W10,[160922_Days.xlsm]Sheet4!I2:L360,4,FALSE),"")

Named Ranges
Name	Refers To	Cells
TMS	=[160922_Days.xlsm]Sheet4!$I$2:$I$360	Q11:V90

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
K10:P10	Expression	=MATCH(K10:P10,INDIRECT("+Sheet4!$I$2:$I$370"),0)	text	NO

 

[Ian1976]

Sorry,didn't know how to post 2 sheets in the same reply?

So basically when I change the the number in K10 on sheet2 I'd like it to be the same colour as the same number in the list on sheet4!

Thanks

 

[SanjayGMusafir]

Example1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
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
Sheet4

Understandably, you are not using rules to conditional format on sheet 4

Are there any rules to format these? they can't be random formats...

Also you still haven't got the solution as yet so remove the solution tick so that the experts or onlookers know that solution is yet to be delivered.

 

[Ian1976]

There are no rules they are just individually colour as required.

Thanks

 

[SanjayGMusafir]

There are no rules they are just individually colour as required.

Thanks

Now I see where we got stuck up.

If there can be pattern why a particular number gets that formatting we can do things very swiftly - Just set the rules and use them anywhere we want

Else copying a format and pasting it can be a tedious job and might require VBA code to do that.

 

[SanjayGMusafir]

Example1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
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
Sheet4

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...

 

[Ian1976]

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