conditional formatting with multiple conditions

[kelvin_9]

greeting to all

is there any better formula/solution i can use instead of these 13 formulas i am using?
i need "abc" or "def" to be green, "UNASSIGNEDSHIFT UNASSIGNEDSHIFT" to be yellow, other than these* to be blue
*sometimes more than 5 criteria

thank you very much for your help

temp.xls
	A	B	C	D	E
2	Part Time - WE		IMX
3	Part Time - Flex		abc
4	Full Time		abc
5	Full Time		abc
6	Full Time		abc
7	Full Time		def
8	Full Time		abc
9	Full Time		abc
10	Full Time		abc
11	Part Time - WE		abc
12	Part Time - WE		abc
13	Full Time		abc
14	Full Time		abc
15	Full Time		abc
16	Full Time		abc
17	Full Time		abc
18	Part Time - WE		abc
19	Part Time - WE		def
20	Part Time - WE		def
21	Part Time - WE		abc
22	Full Time		def
23	Part Time - WE		abc
24	Part Time - WE		def
25	Part Time - WE		def
26	Part Time - WE		abc
27	Full Time		abc
28	Full Time		abc
29	Full Time		def
30	Full Time		def
31	Full Time		def
32	Part Time - WE		abc
33	Part Time - WE		abc
34	Full Time		abc
35	Part Time - WE		def
36	Part Time - WE		abc
37	Part Time - WE		abc
38	Full Time		abc
39	Part Time - WE		def
40	Part Time - WE		abc
41	Part Time - WE		abc
42	Part Time - WE		def
43	Full Time		abc
44	Part Time - WE		abc
45	Part Time - WE		abc
46	Part Time - WE		abc
47	Part Time - WE		abc
48	Part Time - WE		abc
49	Part Time - WE		def
50	Part Time - WE		abc
51	Part Time - WE		abc
52	Part Time - WE		abc
53	Full Time		def
54	Full Time		abc
55	Full Time		abc
56	Full Time		abc
57	Full Time		def
58	Part Time - WE		def
59	Full Time		abc
60	Full Time		abc
61	Full Time		abc
62	Part Time - WE		EMP
63	Part Time - Flex		abc
64	Part Time - Flex		abc
65	Full Time		abc
66	Full Time		abc
67	Full Time		abc
68	Full Time		abc
69	Full Time		abc
70	Full Time		abc
71	Full Time		abc
72	Full Time		def
73	Part Time - WD		abc
74	Full Time		abc
75	Full Time		abc
76	Full Time		abc
77	Full Time		abc
78	Full Time		abc
79	Part Time - WD		abc
80	Full Time		abc
81	Part Time - Flex		abc
82	Part Time - WD		def
83	Full Time		abc
84	Part Time - WD		abc
85	Full Time		def
86	Full Time		def
87	Part Time - Flex		EMP
88	Part Time - Flex		def
89	Full Time		abc
90	Part Time - WD		abc
91	Part Time - WD		abc
92	Part Time - WD		def
93	Part Time - WD		abc
94	Part Time - Flex		abc
95	Part Time - Flex		abc
96	Part Time - WD		def
97	Full Time		abc
98	Full Time		abc
99	Part Time - WD		abc
100	Part Time - WD		abc
101	Part Time - WD		abc
102	Part Time - WD		abc
103	Part Time - Flex		abc
104	Full Time		def
105	Part Time - Flex		abc
106	Full Time		abc
107	Part Time - Flex		abc
108	Part Time - WD		def
109	Full Time		abc
110	Full Time		abc
111	Full Time		def
112	Full Time		abc
113	Full Time		abc
114	Full Time		abc
115	Full Time		abc
116	Full Time		abc
117	Full Time		abc
118	Full Time		abc
119	Part Time - WD		EMP
120	Part Time - Flex		abc
121	Full Time		abc
122	Part Time - WD		abc
123	Full Time		abc
124	Full Time		abc
125	Full Time		abc
126	Full Time		abc
127	Full Time		abc
128	Full Time		abc
129	Full Time		def
130	Full Time		abc
131	Full Time		abc
132	Full Time		abc
133	Full Time		abc
134	Part Time - WD		abc
135	Full Time		abc
136	Part Time - WD		def
137	Part Time - WD		def
138	Full Time		abc
139	Part Time - Flex		abc
140	Full Time		def
141	Full Time		def
142	Part Time - Flex		EMP
143	Part Time - Flex		abc
144	Part Time - WD		abc
145	Full Time		def
146	Part Time - Flex		EMP
147	Part Time - WD		abc
148	Part Time - WD		abc
149	Part Time - WD		abc
150	Part Time - WD		abc
151	Part Time - WD		abc
152	Full Time		def
153	Full Time		def
154					UNASSIGNEDSHIFT UNASSIGNEDSHIFT
155	Part Time - WD		EMP
156	Part Time - WD		EMP
157	Part Time - Flex		EMP
158	Part Time - WD		EMP
159	Part Time - WD		EMP
160	Part Time - Flex		EMP
161	Part Time - WD		EMP
162	Part Time - WD		def
163	Full Time		abc
164	Full Time		abc
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E2:E10000	Expression	=AND(A2="Part Time - WD",C2="imx")	text	YES
E2:E10000	Expression	=AND(A2="Part Time - Flex",C2="imx")	text	YES
E2:E10000	Expression	=AND(A2="Part Time - WE",C2="imx")	text	YES
E2:E10000	Expression	=AND(A2="Part Time - WD",C2="emp")	text	YES
E2:E10000	Expression	=AND(A2="Part Time - Flex",C2="emp")	text	YES
E2:E10000	Expression	=AND(A2="Part Time - WE",C2="emp")	text	YES
E2:E10000	Expression	=AND(A2="",C2="",E2="UNASSIGNEDSHIFT UNASSIGNEDSHIFT")	text	YES
E2:E10000	Expression	=AND(A2="Part Time - WD",C2="def")	text	YES
E2:E10000	Expression	=AND(A2="Part Time - Flex",C2="def")	text	YES
E2:E10000	Expression	=AND(A2="Part Time - WE",C2="def")	text	YES
E2:E10000	Expression	=AND(A2="Part Time - WD",C2="abc")	text	YES
E2:E10000	Expression	=AND(A2="Part Time - Flex",C2="abc")	text	YES
E2:E10000	Expression	=AND(A2="Part Time - WE",C2="abc")	text	YES

 

[Peter_SSs]

i need "abc" or "def" to be green, "UNASSIGNEDSHIFT UNASSIGNEDSHIFT" to be yellow, other than these* to be blue

Not quite sure about this as it does not match your mini sheet, but do these three rules help?

25 01 26.xlsm
	A	B	C	D	E
2	Part Time - WE		IMX
3	Part Time - Flex		abc
4	Full Time		abc
5	Full Time		abc
6	Full Time		abc
7	Full Time		def
8	Full Time		abc
9	Full Time		abc
10	Full Time		abc
11	Part Time - WE		abc
12	Part Time - WE		abc
13	Full Time		abc
14	Full Time		abc
15	Full Time		abc
16	Full Time		abc
17	Full Time		abc
18	Part Time - WE		abc
19	Part Time - WE		def
20	Part Time - WE		def
21	Part Time - WE		abc
22	Full Time		def
23	Part Time - WE		abc
24	Part Time - WE		def
25	Part Time - WE		def
26	Part Time - WE		abc
27	Full Time		abc
28	Full Time		abc
29	Full Time		def
30	Full Time		def
31	Full Time		def
32	Part Time - WE		abc
33	Part Time - WE		abc
34	Full Time		abc
35	Part Time - WE		def
36	Part Time - WE		abc
37	Part Time - WE		abc
38	Full Time		abc
39	Part Time - WE		def
40	Part Time - WE		abc
41	Part Time - WE		abc
42	Part Time - WE		def
43	Full Time		abc
44	Part Time - WE		abc
45	Part Time - WE		abc
46	Part Time - WE		abc
47	Part Time - WE		abc
48	Part Time - WE		abc
49	Part Time - WE		def
50	Part Time - WE		abc
51	Part Time - WE		abc
52	Part Time - WE		abc
53	Full Time		def
54	Full Time		abc
55	Full Time		abc
56	Full Time		abc
57	Full Time		def
58	Part Time - WE		def
59	Full Time		abc
60	Full Time		abc
61	Full Time		abc
62	Part Time - WE		EMP
63	Part Time - Flex		abc
64	Part Time - Flex		abc
65	Full Time		abc
66	Full Time		abc
67	Full Time		abc
68	Full Time		abc
69	Full Time		abc
70	Full Time		abc
71	Full Time		abc
72	Full Time		def
73	Part Time - WD		abc
74	Full Time		abc
75	Full Time		abc
76	Full Time		abc
77	Full Time		abc
78	Full Time		abc
79	Part Time - WD		abc
80	Full Time		abc
81	Part Time - Flex		abc
82	Part Time - WD		def
83	Full Time		abc
84	Part Time - WD		abc
85	Full Time		def
86	Full Time		def
87	Part Time - Flex		EMP
88	Part Time - Flex		def
89	Full Time		abc
90	Part Time - WD		abc
91	Part Time - WD		abc
92	Part Time - WD		def
93	Part Time - WD		abc
94	Part Time - Flex		abc
95	Part Time - Flex		abc
96	Part Time - WD		def
97	Full Time		abc
98	Full Time		abc
99	Part Time - WD		abc
100	Part Time - WD		abc
101	Part Time - WD		abc
102	Part Time - WD		abc
103	Part Time - Flex		abc
104	Full Time		def
105	Part Time - Flex		abc
106	Full Time		abc
107	Part Time - Flex		abc
108	Part Time - WD		def
109	Full Time		abc
110	Full Time		abc
111	Full Time		def
112	Full Time		abc
113	Full Time		abc
114	Full Time		abc
115	Full Time		abc
116	Full Time		abc
117	Full Time		abc
118	Full Time		abc
119	Part Time - WD		EMP
120	Part Time - Flex		abc
121	Full Time		abc
122	Part Time - WD		abc
123	Full Time		abc
124	Full Time		abc
125	Full Time		abc
126	Full Time		abc
127	Full Time		abc
128	Full Time		abc
129	Full Time		def
130	Full Time		abc
131	Full Time		abc
132	Full Time		abc
133	Full Time		abc
134	Part Time - WD		abc
135	Full Time		abc
136	Part Time - WD		def
137	Part Time - WD		def
138	Full Time		abc
139	Part Time - Flex		abc
140	Full Time		def
141	Full Time		def
142	Part Time - Flex		EMP
143	Part Time - Flex		abc
144	Part Time - WD		abc
145	Full Time		def
146	Part Time - Flex		EMP
147	Part Time - WD		abc
148	Part Time - WD		abc
149	Part Time - WD		abc
150	Part Time - WD		abc
151	Part Time - WD		abc
152	Full Time		def
153	Full Time		def
154					UNASSIGNEDSHIFT UNASSIGNEDSHIFT
155	Part Time - WD		EMP
156	Part Time - WD		EMP
157	Part Time - Flex		EMP
158	Part Time - WD		EMP
159	Part Time - WD		EMP
160	Part Time - Flex		EMP
161	Part Time - WD		EMP
162	Part Time - WD		def
163	Full Time		abc
164	Full Time		abc
CF

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E2:E1000	Expression	=AND(A2="",C2="",E2="UNASSIGNEDSHIFT UNASSIGNEDSHIFT")	text	NO
E2:E1000	Expression	=AND(LEFT(A2,1)="P",SEARCH("|"&C2&"|","|emp|imx|"))	text	NO
E2:E1000	Expression	=AND(LEFT(A2,1)="P",SEARCH("|"&C2&"|","|abc|def|"))	text	NO

 

[kelvin_9]

Not quite sure about this as it does not match your mini sheet, but do these three rules help?

25 01 26.xlsm
	A	B	C	D	E
2	Part Time - WE		IMX
3	Part Time - Flex		abc
4	Full Time		abc
5	Full Time		abc
6	Full Time		abc
7	Full Time		def
8	Full Time		abc
9	Full Time		abc
10	Full Time		abc
11	Part Time - WE		abc
12	Part Time - WE		abc
13	Full Time		abc
14	Full Time		abc
15	Full Time		abc
16	Full Time		abc
17	Full Time		abc
18	Part Time - WE		abc
19	Part Time - WE		def
20	Part Time - WE		def
21	Part Time - WE		abc
22	Full Time		def
23	Part Time - WE		abc
24	Part Time - WE		def
25	Part Time - WE		def
26	Part Time - WE		abc
27	Full Time		abc
28	Full Time		abc
29	Full Time		def
30	Full Time		def
31	Full Time		def
32	Part Time - WE		abc
33	Part Time - WE		abc
34	Full Time		abc
35	Part Time - WE		def
36	Part Time - WE		abc
37	Part Time - WE		abc
38	Full Time		abc
39	Part Time - WE		def
40	Part Time - WE		abc
41	Part Time - WE		abc
42	Part Time - WE		def
43	Full Time		abc
44	Part Time - WE		abc
45	Part Time - WE		abc
46	Part Time - WE		abc
47	Part Time - WE		abc
48	Part Time - WE		abc
49	Part Time - WE		def
50	Part Time - WE		abc
51	Part Time - WE		abc
52	Part Time - WE		abc
53	Full Time		def
54	Full Time		abc
55	Full Time		abc
56	Full Time		abc
57	Full Time		def
58	Part Time - WE		def
59	Full Time		abc
60	Full Time		abc
61	Full Time		abc
62	Part Time - WE		EMP
63	Part Time - Flex		abc
64	Part Time - Flex		abc
65	Full Time		abc
66	Full Time		abc
67	Full Time		abc
68	Full Time		abc
69	Full Time		abc
70	Full Time		abc
71	Full Time		abc
72	Full Time		def
73	Part Time - WD		abc
74	Full Time		abc
75	Full Time		abc
76	Full Time		abc
77	Full Time		abc
78	Full Time		abc
79	Part Time - WD		abc
80	Full Time		abc
81	Part Time - Flex		abc
82	Part Time - WD		def
83	Full Time		abc
84	Part Time - WD		abc
85	Full Time		def
86	Full Time		def
87	Part Time - Flex		EMP
88	Part Time - Flex		def
89	Full Time		abc
90	Part Time - WD		abc
91	Part Time - WD		abc
92	Part Time - WD		def
93	Part Time - WD		abc
94	Part Time - Flex		abc
95	Part Time - Flex		abc
96	Part Time - WD		def
97	Full Time		abc
98	Full Time		abc
99	Part Time - WD		abc
100	Part Time - WD		abc
101	Part Time - WD		abc
102	Part Time - WD		abc
103	Part Time - Flex		abc
104	Full Time		def
105	Part Time - Flex		abc
106	Full Time		abc
107	Part Time - Flex		abc
108	Part Time - WD		def
109	Full Time		abc
110	Full Time		abc
111	Full Time		def
112	Full Time		abc
113	Full Time		abc
114	Full Time		abc
115	Full Time		abc
116	Full Time		abc
117	Full Time		abc
118	Full Time		abc
119	Part Time - WD		EMP
120	Part Time - Flex		abc
121	Full Time		abc
122	Part Time - WD		abc
123	Full Time		abc
124	Full Time		abc
125	Full Time		abc
126	Full Time		abc
127	Full Time		abc
128	Full Time		abc
129	Full Time		def
130	Full Time		abc
131	Full Time		abc
132	Full Time		abc
133	Full Time		abc
134	Part Time - WD		abc
135	Full Time		abc
136	Part Time - WD		def
137	Part Time - WD		def
138	Full Time		abc
139	Part Time - Flex		abc
140	Full Time		def
141	Full Time		def
142	Part Time - Flex		EMP
143	Part Time - Flex		abc
144	Part Time - WD		abc
145	Full Time		def
146	Part Time - Flex		EMP
147	Part Time - WD		abc
148	Part Time - WD		abc
149	Part Time - WD		abc
150	Part Time - WD		abc
151	Part Time - WD		abc
152	Full Time		def
153	Full Time		def
154					UNASSIGNEDSHIFT UNASSIGNEDSHIFT
155	Part Time - WD		EMP
156	Part Time - WD		EMP
157	Part Time - Flex		EMP
158	Part Time - WD		EMP
159	Part Time - WD		EMP
160	Part Time - Flex		EMP
161	Part Time - WD		EMP
162	Part Time - WD		def
163	Full Time		abc
164	Full Time		abc
CF

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E2:E1000	Expression	=AND(A2="",C2="",E2="UNASSIGNEDSHIFT UNASSIGNEDSHIFT")	text	NO
E2:E1000	Expression	=AND(LEFT(A2,1)="P",SEARCH("|"&C2&"|","|emp|imx|"))	text	NO
E2:E1000	Expression	=AND(LEFT(A2,1)="P",SEARCH("|"&C2&"|","|abc|def|"))	text	NO

thank you very much for you reply, Peter_SSs

this is just prefect and doing what i am doing in these 13 formulas
save my life!!

i am curious if i has another crietria like i said, is it simply add like:
AND(LEFT(A2,1)="P",SEARCH("|"&C2&"|","|emp|imx|aaa|zzz"))

last but not least
i just add "no" in column J to identify who are non-cashier skill set and i want this result to be grey in column E
what should i need to modify this?
=AND(LEFT(A2,1)="P",SEARCH("|"&C2&"|","|abc|def|"))

thank you very much for you guidance

temp.xls
	A	B	C	D	E	F	G	H	I	J
2	Part Time - WE		IMX			12/29/2024		8:15	8:45
3	Part Time - Flex		abc			12/29/2024		8:15	17:45	no
4	Full Time		abc			12/29/2024		8:15	17:45
5	Full Time		abc			12/29/2024		8:15	17:45
6	Full Time		abc			12/29/2024		8:15	17:45
7	Full Time		def			12/29/2024		8:15	17:45
8	Full Time		abc			12/29/2024		8:15	17:45
9	Full Time		abc			12/29/2024		8:30	18:00
10	Full Time		abc			12/29/2024		8:30	18:00
11	Part Time - WE		abc			12/29/2024		8:30	18:00	no
12	Part Time - WE		abc			12/29/2024		8:30	18:00	no
13	Full Time		abc			12/29/2024		8:30	18:00
14	Full Time		abc			12/29/2024		8:30	18:00
15	Full Time		abc			12/29/2024		8:30	18:00
16	Full Time		abc			12/29/2024		8:30	18:00
17	Full Time		abc			12/29/2024		8:30	18:00
18	Part Time - WE		abc			12/29/2024		9:00	18:30
19	Part Time - WE		def			12/29/2024		9:00	18:30
20	Part Time - WE		def			12/29/2024		9:00	18:30
21	Part Time - WE		abc			12/29/2024		9:00	18:30
22	Full Time		def			12/29/2024		9:00	18:30
23	Part Time - WE		abc			12/29/2024		9:00	18:30
24	Part Time - WE		def			12/29/2024		9:00	18:30
25	Part Time - WE		def			12/29/2024		9:00	18:30
26	Part Time - WE		abc			12/29/2024		9:00	18:30
27	Full Time		abc			12/29/2024		9:00	18:30
28	Full Time		abc			12/29/2024		9:00	18:30
29	Full Time		def			12/29/2024		9:00	18:30
30	Full Time		def			12/29/2024		9:00	18:30
31	Full Time		def			12/29/2024		9:00	18:30
32	Part Time - WE		abc			12/29/2024		9:00	18:30
33	Part Time - WE		abc			12/29/2024		9:00	18:30
34	Full Time		abc			12/29/2024		9:00	18:30
35	Part Time - WE		def			12/29/2024		9:00	18:30
36	Part Time - WE		abc			12/29/2024		9:00	18:30
37	Part Time - WE		abc			12/29/2024		9:00	18:30
38	Full Time		abc			12/29/2024		10:00	19:30
39	Part Time - WE		def			12/29/2024		10:15	17:45
40	Part Time - WE		abc			12/29/2024		12:00	21:30
41	Part Time - WE		abc			12/29/2024		12:00	21:30
42	Part Time - WE		def			12/29/2024		12:00	21:30
43	Full Time		abc			12/29/2024		12:00	21:30
44	Part Time - WE		abc			12/29/2024		12:30	22:00
45	Part Time - WE		abc			12/29/2024		12:30	22:00
46	Part Time - WE		abc			12/29/2024		12:30	22:00
47	Part Time - WE		abc			12/29/2024		13:45	23:15
48	Part Time - WE		abc			12/29/2024		13:45	23:15
49	Part Time - WE		def			12/29/2024		13:45	23:15
50	Part Time - WE		abc			12/29/2024		13:45	23:15
51	Part Time - WE		abc			12/29/2024		13:45	23:15
52	Part Time - WE		abc			12/29/2024		13:45	23:15
53	Full Time		def			12/29/2024		13:45	23:15
54	Full Time		abc			12/29/2024		13:45	23:15
55	Full Time		abc			12/29/2024		13:45	23:15
56	Full Time		abc			12/29/2024		13:45	23:15
57	Full Time		def			12/29/2024		13:45	23:15
58	Part Time - WE		def			12/29/2024		13:45	23:15
59	Full Time		abc			12/29/2024		13:45	23:15
60	Full Time		abc			12/29/2024		13:45	23:15
61	Full Time		abc			12/29/2024		13:45	23:15
62	Part Time - WE		EMP			12/29/2024		16:30	23:15
63	Part Time - Flex		abc			12/30/2024		8:15	8:45
64	Part Time - Flex		abc			12/30/2024		8:15	17:45
65	Full Time		abc			12/30/2024		8:15	17:45
66	Full Time		abc			12/30/2024		8:15	17:45
67	Full Time		abc			12/30/2024		8:15	17:45
68	Full Time		abc			12/30/2024		8:15	17:45
69	Full Time		abc			12/30/2024		8:15	17:45
70	Full Time		abc			12/30/2024		8:30	18:00
71	Full Time		abc			12/30/2024		8:30	18:00
72	Full Time		def			12/30/2024		8:30	18:00
73	Part Time - WD		abc			12/30/2024		8:30	18:00	no
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E2:E10000	Expression	=AND(A2="Part Time - WD",C2="imx")	text	YES
E2:E10000	Expression	=AND(A2="Part Time - Flex",C2="imx")	text	YES
E2:E10000	Expression	=AND(A2="Part Time - WE",C2="imx")	text	YES
E2:E10000	Expression	=AND(A2="Part Time - WD",C2="emp")	text	YES
E2:E10000	Expression	=AND(A2="Part Time - Flex",C2="emp")	text	YES
E2:E10000	Expression	=AND(A2="Part Time - WE",C2="emp")	text	YES
E2:E10000	Expression	=AND(A2="",C2="",E2="UNASSIGNEDSHIFT UNASSIGNEDSHIFT")	text	YES
E2:E10000	Expression	=AND(A2="Part Time - WD",C2="def")	text	YES
E2:E10000	Expression	=AND(A2="Part Time - Flex",C2="def")	text	YES
E2:E10000	Expression	=AND(A2="Part Time - WE",C2="def")	text	YES
E2:E10000	Expression	=AND(A2="Part Time - WD",C2="abc")	text	YES
E2:E10000	Expression	=AND(A2="Part Time - Flex",C2="abc")	text	YES
E2:E10000	Expression	=AND(A2="Part Time - WE",C2="abc")	text	YES

 

[Peter_SSs]

this is just prefect and doing what i am doing in these 13 formulas
save my life!!

Good news!

i am curious if i has another crietria like i said, is it simply add like:
AND(LEFT(A2,1)="P",SEARCH("|"&C2&"|","|emp|imx|aaa|zzz"))

You would use that to highlight any rows where column A started with a "P" and column C was any one of those items listed at the end, except that you must add the extra "|" character at the end so it would actually be
AND(LEFT(A2,1)="P",SEARCH("|"&C2&"|","|emp|imx|aaa|zzz|"))

last but not least
i just add "no" in column J to identify who are non-cashier skill set and i want this result to be grey in column E
what should i need to modify this?
=AND(LEFT(A2,1)="P",SEARCH("|"&C2&"|","|abc|def|"))

This is not clear

i just add "no" in column J to identify who are non-cashier skill set and i want this result to be grey in column E

This implies grey if column J is "no" no mater what is in the other columns.

what should i need to modify this?
=AND(LEFT(A2,1)="P",SEARCH("|"&C2&"|","|abc|def|"))

This, combined with the sentence above, suggests that you may want a "P" in column A and "abc" or "def" in column C and "no" in column J before you make that row grey.

Which is it?

 

[kelvin_9]

Good news!



You would use that to highlight any rows where column A started with a "P" and column C was any one of those items listed at the end, except that you must add the extra "|" character at the end so it would actually be
AND(LEFT(A2,1)="P",SEARCH("|"&C2&"|","|emp|imx|aaa|zzz|"))



This is not clear


This implies grey if column J is "no" no mater what is in the other columns.


This, combined with the sentence above, suggests that you may want a "P" in column A and "abc" or "def" in column C and "no" in column J before you make that row grey.

Which is it?

thank you very much for your reply, Peter_SSs

yes, your suggest is correct
i want "P" in column A and "abc" or "def" (or even more cretrias) in coulmn C and "no" in column J before E2:E10000 change to grey

thank you very much for you guidance

 

[Peter_SSs]

Thanks for the clarification. Add a new rule for the grey. The CF formula for the new rule would be like this.

=AND(LEFT(A2,1)="P",SEARCH("|"&C2&"|","|abc|def|"),J2="no")

 

[kelvin_9]

Thanks for the clarification. Add a new rule for the grey. The CF formula for the new rule would be like this.

=AND(LEFT(A2,1)="P",SEARCH("|"&C2&"|","|abc|def|"),J2="no")

thank you very much for your reply, Peter_SSs

it works just prefect

thank you very much

 

[Peter_SSs]

Cheers. Glad to help.