Condtional Formatting formula help

[MeaclH]

G'day all,

I am in need of one final formula for a project I have been working on.
The range of cells in question that need formatting are G19:T19,G22:T22,G25:T25,G28:T28,G31:T31,G35:T35,G38:T38,G41:T41,G44:T44,G47:T47,G50:T50,G53:T53,G56:T56,G59:T59,G62:T62,G65:T65,G68:T68,G71:T71,G74:T74,G77:T77,G80:T80,G83:T83,G86:T86,G89:T89,G92:T92,G96:T96,G99:T99,G102:T102,G105:T105,G108:T108,G112:T112.

An example of the criteria would be
IF K96 contains X0530 and if there is a value containing 0530 in K5:K9, then K96 is highlighted.
I would want the same to apply if the value in K96 was X1330, it searched for values containing 1330 and also X2130 search for values containing 2130.

Hopefully that's easy to follow.

Appreciate your help.
Cheers,
Hayden

DIGITAL ROSTER - VERSION 3.0.xlsm
	K	L	M
2	THU	FRI	SAT
3	13-Mar	14-Mar	15-Mar
4
5	0530PL/S	0530PB	2130PB
6	1330BC	1330PB
7
8
9
10
11
12
13
14
15
16
17
18
19	0530TL	OFF	OFF
20
21
22	1330TL	1330TL	1330TL
23
24
25	2130TL	2130TL	2130TL
26
27
28	OFF	OFF	OFF
29
30
31	0700AD	0530TL	0530TL
32
33
34
35	1330PL/S	EDO	OFF
36
37
38	0530NRN	0530BC	0530PB
39
40
41	OFF	2130PB	2130NCC
42
43
44	1330NRN	1330BC	1330PB
45
46
47	2130PB	2130NCC	2130PL/S
48
49
50	OFF	0530NRN	0530BC
51
52
53	EDO	OFF	OFF
54
55
56	2130NCC	2130PL/S	2130BC
57
58
59	0530PB	0530CFD	OFF
60
61
62	OFF	1330NRN	1330BC
63
64
65	EDO	OFF	OFF
66
67
68	0530CFD	0530PL/S	0530NCC
69
70
71	EDO	OFF	OFF
72
73
74	1330CFD	1330PL/S	OFF
75
76
77	1330PB	1330CFD	1330PL/S
78
79
80	2130PL/S	2130BC
81
82
83			1330NCC
84
85
86	0530BC		OFF
87
88
89	2130BC	OFF	OFF
90
91
92		OFF	0530PL/S
93
94
95
96	X0530	X0530	AV
97
98
99	X1330	X1330
100
101
FN 09MAR-22MAR25

Cell Formulas
Range	Formula
K5:L6	K5	=FILTER(DATA!$AM$3:$AM$19,NOT(COUNTIF(K11:K111,DATA!$AM$3:$AM$19)),"")
M5	M5	=FILTER(DATA!$AL$3:$AL$17,NOT(COUNTIF(M11:M111,DATA!$AL$3:$AL$17)),"")
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G19:T19,G22:T22,G25:T25,G28:T28,G31:T31,G35:T35,G38:T38,G41:T41,G44:T44,G47:T47,G50:T50,G53:T53,G56:T56,G59:T59,G62:T62,G65:T65,G68:T68,G71:T71,G74:T74,G77:T77,G80:T80,G83:T83,G86:T86,G89:T89,G92:T92,G96:T96,G99:T99,G102:T102,G105:T105,G108:T108,G112:T112	Expression	=AND(COUNTIF($AU$18:$AU$44,G19)=0,COUNTIF(G$19:G$110,G19)>1)	text	NO
G19:T19,G22:T22,G25:T25,G28:T28,G31:T31,G35:T35,G38:T38,G41:T41,G44:T44,G47:T47,G50:T50,G53:T53,G56:T56,G59:T59,G62:T62,G65:T65,G68:T68,G71:T71,G74:T74,G77:T77,G80:T80,G83:T83,G86:T86,G89:T89,G92:T92,G96:T96,G99:T99,G102:T102,G105:T105,G108:T108,G112:T112	Cell Value	contains "EDO-U"	text	YES
G19:T19,G22:T22,G25:T25,G28:T28,G31:T31,G35:T35,G38:T38,G41:T41,G44:T44,G47:T47,G50:T50,G53:T53,G56:T56,G59:T59,G62:T62,G65:T65,G68:T68,G71:T71,G74:T74,G77:T77,G80:T80,G83:T83,G86:T86,G89:T89,G92:T92,G96:T96,G99:T99,G102:T102,G105:T105,G108:T108,G112:T112	Cell Value	contains "OFF-U"	text	YES
G19:T19,G22:T22,G25:T25,G28:T28,G31:T31,G35:T35,G38:T38,G41:T41,G44:T44,G47:T47,G50:T50,G53:T53,G56:T56,G59:T59,G62:T62,G65:T65,G68:T68,G71:T71,G74:T74,G77:T77,G80:T80,G83:T83,G86:T86,G89:T89,G92:T92,G96:T96,G99:T99,G102:T102,G105:T105,G108:T108,G112:T112	Expression	=OR(COUNTIF(G19,"*BLV*"), COUNTIF(G19,"*CDO*"), COUNTIF(G19,"*SDO*"), COUNTIF(G19,"*PDO*"), COUNTIF(G19,"*TFN*"))	text	YES
G19:T19,G22:T22,G25:T25,G28:T28,G31:T31,G35:T35,G38:T38,G41:T41,G44:T44,G47:T47,G50:T50,G53:T53,G56:T56,G59:T59,G62:T62,G65:T65,G68:T68,G71:T71,G74:T74,G77:T77,G80:T80,G83:T83,G86:T86,G89:T89,G92:T92,G96:T96,G99:T99,G102:T102,G105:T105,G108:T108,G112:T112	Expression	=OR(COUNTIF(G19,"*A/L*"), COUNTIF(G19,"*BONUS*"), COUNTIF(G19,"*GAZETTE*"), COUNTIF(G19,"*LSL*"), COUNTIF(G19,"*MATLVE*"), COUNTIF(G19,"*PATLVE*"), COUNTIF(G19,"*PHC*"))	text	YES
G19:T19,G22:T22,G25:T25,G28:T28,G31:T31,G35:T35,G38:T38,G41:T41,G44:T44,G47:T47,G50:T50,G53:T53,G56:T56,G59:T59,G62:T62,G65:T65,G68:T68,G71:T71,G74:T74,G77:T77,G80:T80,G83:T83,G86:T86,G89:T89,G92:T92,G96:T96,G99:T99,G102:T102,G105:T105,G108:T108,G112:T112	Cell Value	contains "OFF"	text	YES
G19:T19,G22:T22,G25:T25,G28:T28,G31:T31,G35:T35,G38:T38,G41:T41,G44:T44,G47:T47,G50:T50,G53:T53,G56:T56,G59:T59,G62:T62,G65:T65,G68:T68,G71:T71,G74:T74,G77:T77,G80:T80,G83:T83,G86:T86,G89:T89,G92:T92,G96:T96,G99:T99,G102:T102,G105:T105,G108:T108,G112:T112	Cell Value	contains "EDO"	text	YES
G23:T23,G26:T26,G29:T29,G32:T32,G36:T36,G39:T39,G42:T42,G45:T45,G48:T48,G51:T51,G54:T54,G57:T57,G60:T60,G63:T63,G66:T66,G69:T69,G72:T72,G75:T75,G78:T78,G81:T81,G84:T84,G87:T87,G90:T90,G93:T93,G97:T97,G100:T100,G103:T103,G106:T106,G109:T109,G113:T113	Cell Value	ending with "?"	text	NO
G35:T94,G96:T110,G112:T126,G22:T33,G19:T19,G20,H20:T21	Cell Value	contains "AV"	text	NO
G11:T14,G16:T17,G19:T33,G35:T94,G96:T110,G112:T126,G5:T9,G2:T3	Expression	=G$3=TODAY()	text	NO
G11:T14,G16:T17,G19:T33,G35:T94,G96:T110,G112:T126,G5:T9,G2:T3	Expression	=COLUMN()=CELL("COL")	text	NO
G5:T5	Cell	contains an error	text	NO
G2:T3,G11:T14,G16:T17,G19:T33,G35:T94,G96:T110,G112:T126,G5:T9	Expression	=COUNTIF(DATA!$AV$4:$AV$16,G$3)=1	text	NO
G23:T23,G26:T26,G29:T29,G32:T32,G36:T36,G39:T39,G42:T42,G45:T45,G48:T48,G51:T51,G54:T54,G57:T57,G60:T60,G63:T63,G66:T66,G69:T69,G72:T72,G75:T75,G78:T78,G81:T81,G84:T84,G87:T87,G90:T90,G93:T93,G97:T97,G100:T100,G103:T103,G106:T106,G109:T109,G113:T113	Cell Value	contains "OK"	text	NO
G23:T23,G26:T26,G29:T29,G32:T32,G36:T36,G39:T39,G42:T42,G45:T45,G48:T48,G51:T51,G54:T54,G57:T57,G60:T60,G63:T63,G66:T66,G69:T69,G72:T72,G75:T75,G78:T78,G81:T81,G84:T84,G87:T87,G90:T90,G93:T93,G97:T97,G100:T100,G103:T103,G106:T106,G109:T109,G113:T113	Cell Value	contains "DEC"	text	NO

Cells with Data Validation
Cell	Allow	Criteria
K25:M25	List	=DATA!$AF$4:$AF$56
K35:M35	List	=DATA!$AF$4:$AF$56
K96:M96	List	=DATA!$AF$4:$AF$56
K99:M99	List	=DATA!$AF$4:$AF$56
K44:M44	List	=DATA!$AF$4:$AF$56
K47:M47	List	=DATA!$AF$4:$AF$56
K50:M50	List	=DATA!$AF$4:$AF$56
K53:M53	List	=DATA!$AF$4:$AF$56
K56:M56	List	=DATA!$AF$4:$AF$56
K59:M59	List	=DATA!$AF$4:$AF$56
K62:M62	List	=DATA!$AF$4:$AF$56
K65:M65	List	=DATA!$AF$4:$AF$56
K68:M68	List	=DATA!$AF$4:$AF$56
K71:M71	List	=DATA!$AF$4:$AF$56
K74:M74	List	=DATA!$AF$4:$AF$56
K77:M77	List	=DATA!$AF$4:$AF$56
K80:M80	List	=DATA!$AF$4:$AF$56
K83:M83	List	=DATA!$AF$4:$AF$56
K86:M86	List	=DATA!$AF$4:$AF$56
K89:M89	List	=DATA!$AF$4:$AF$56
K31:M31	List	=DATA!$AF$4:$AF$56

 

[StephenCrump]

Based on this description, you could conditionally format K96 with the formula:

=OR(ISNUMBER(FIND(MID(K96,2,99),K5:K9)))

This formula is looking at values around 90 rows above, so it's not clear which cells you want to point to for conditional formatting in:

G19:T19,G22:T22,G25:T25,G28:T28,G31:T31 ..... etc?

 

[MeaclH]

Based on this description, you could conditionally format K96 with the formula:

=OR(ISNUMBER(FIND(MID(K96,2,99),K5:K9)))

This formula is looking at values around 90 rows above, so it's not clear which cells you want to point to for conditional formatting in:

G19:T19,G22:T22,G25:T25,G28:T28,G31:T31 ..... etc?

Hi Stephen,
I managed to figure out a solution that appears to be working well. I'm aware there is probably a more efficient formula to use, but this is what I came up with.

=IF(G19="X2130",COUNTIF(G$5:G$9,"*2130*")>=1)

I then duplicated the rule twice for each other instance (0530 and 1330)

Appreciate your feedback.

Cheers,
Hayden