Conditionally format worksheet text from multiple cells containing text.

[bradjsteve88]

I am looking to clean up the conditional formatting as this document will be used on multiple projects moving forward.

I am wanting to know if it is possible to have the rule CONTAINING TEXT 'FORMAT ONLY CELLS THAT CONTAIN" for;

• 'Cell Value contains = $R$8 to instead be
• Cell Value contains = '$R$8','$R$20','$R$21','$R$22','$R$23','$R$24' if possible.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1
2																	DO NOT EDIT PAST THIS COLUMN
3																	14-02-22
4			DESCRIPTION	CONTRACT ITEM (IF APPLICABLE)	CAPTURED BY	IDENTIFIED	FORECAST COMPLETION	ACTUAL COMP	DAYS OPEN	NEXT ACTION Team Lead	RESPONSE BY	STATUS	COMMENT
5																	LIST
6			HIGH LEVEL RISKS															COMPANY NAME
7	R	001			AL	09-Feb-22	19-Feb-22		4	AS	14-Feb-22	TRACKING					AL	AL
8	R	002			AL	11-Feb-22	30-Apr-22		2	AS		TRACKING					CLIENT	MT
9	R	003															END USER	AD
10	R	004															CONTRACTORS	AC
11	R	005
12	R	006
13
14			PROJECT GAPS															LEADS
15	PG	001			AD	04-Feb-22			7			TRACKING					AL	AA
16	PG	002			AD	11-Feb-22			2			TRACKING						AB
17	PG	003			AC	11-Feb-22			2	NF		TRACKING						AC
18	PG	004			AD	11-Feb-22			2			TRACKING						AD
19	PG	005			AC	11-Feb-22			2	NF		TRACKING						AE
20	PG	006															CLIENT	NF
21	PG	007																PS
22	PG	008																ES
23	PG	009																HF
24	PG	010																RH
25	PG	011															END USER	DT
26																		DT
27																		DT
28
29
30																	CONTRACTORS	DS
31																		RN
32																		FE
33																		SS
34																		AB
35
36
37
38
Risk Register

Cell Formulas
Range	Formula
Q3	Q3	=TODAY()
K7	K7	=Q3
B8:B12,B16:B25	B8	=B7+1
I7:I25	I7	= IF(H7>0,NETWORKDAYS(F7,H7), IF(F7>0,NETWORKDAYS(F7,$Q$3), IF(F7=0," ","")))
L7	L7	=IF(Q3<K7,"RESP.LATE", IF(H7>0,"CLOSED", IF(F7>0,"TRACKING", "")))
L8:L25	L8	=IF(H8>0,"CLOSED",IF(F8>0,"TRACKING",""))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
K7:K25	Cell Value	between TODAY()-1 and "today()-365"	text	NO
K7:K25	Cell Value	between TODAY() and "today()+7"	text	NO
K7:K25	Cell Value	between TODAY()+7 and "today()+30"	text	NO
K7:K25	Cell Value	between TODAY()+31 and "today()+90"	text	NO
J7:J25,L7:L25	Cell Value	contains "CLOSED"	text	NO
J7:J25,L7:L25	Cell Value	contains "TRACKING"	text	NO
E:L	Cell Value	contains ""	text	NO
E7:L25	Cell Value	contains ""	text	NO
E7:L25	Cell Value	contains ""	text	NO
E7:L25	Cell Value	contains ""	text	NO
E7:L25	Cell Value	contains ""	text	NO
E7:L25	Cell Value	contains ""	text	NO
E7:L25	Cell Value	contains ""	text	NO
E7:L25	Cell Value	contains ""	text	NO
E7:L25	Cell Value	contains ""	text	NO
E7:L25	Cell Value	contains ""	text	NO
E7:L25	Cell Value	contains ""	text	NO
E7:L25	Cell Value	contains ""	text	NO
E7:L25	Cell Value	contains ""	text	NO
E7:L25	Cell Value	contains ""	text	NO
E7:L25	Cell Value	contains ""	text	NO
E7:L25	Cell Value	contains ""	text	NO
E7:L25	Cell Value	contains ""	text	NO
E15:E25,E4,E6:E13	Cell Value	contains ""	text	NO
E15:E25,E4,E6:E13	Cell Value	contains ""	text	NO
E15:E25,E4,E6:E13	Cell Value	contains ""	text	NO
E15:E25,E4,E6:E13	Cell Value	contains ""	text	NO
E15:E25,E4,E6:E13	Cell Value	contains ""	text	NO
E15:E25,E4,E6:E13	Cell Value	contains ""	text	NO

Cells with Data Validation
Cell	Allow	Criteria
J7:J25	List	=LEADS
E7:E25	List	=COMPANIES

 

[jtakw]

I am looking to clean up the conditional formatting as this document will be used on multiple projects moving forward.

I am wanting to know if it is possible to have the rule CONTAINING TEXT 'FORMAT ONLY CELLS THAT CONTAIN" for;

• 'Cell Value contains = $R$8 to instead be
• Cell Value contains = '$R$8','$R$20','$R$21','$R$22','$R$23','$R$24' if possible.

Hi,

It would be =OR(?cell=$R$8,?cell=$R$20,?cell=$R$21,etc......

where ?cell is your subject cell reference.