Condtional Formatting formula help

MeaclH

Board Regular
Joined
Apr 2, 2014
Messages
111
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
KLM
2THUFRISAT
313-Mar14-Mar15-Mar
4
50530PL/S0530PB2130PB
61330BC1330PB
7
8
9
10
11
12
13
14
15
16
17
18
190530TLOFFOFF
20
21
221330TL1330TL1330TL
23
24
252130TL2130TL2130TL
26
27
28OFFOFFOFF
29
30
310700AD0530TL0530TL
32
33
34
351330PL/SEDOOFF
36
37
380530NRN0530BC0530PB
39
40
41OFF2130PB2130NCC
42
43
441330NRN1330BC1330PB
45
46
472130PB2130NCC2130PL/S
48
49
50OFF0530NRN0530BC
51
52
53EDOOFFOFF
54
55
562130NCC2130PL/S2130BC
57
58
590530PB0530CFDOFF
60
61
62OFF1330NRN1330BC
63
64
65EDOOFFOFF
66
67
680530CFD0530PL/S0530NCC
69
70
71EDOOFFOFF
72
73
741330CFD1330PL/SOFF
75
76
771330PB1330CFD1330PL/S
78
79
802130PL/S2130BC
81
82
831330NCC
84
85
860530BCOFF
87
88
892130BCOFFOFF
90
91
92OFF0530PL/S
93
94
95
96X0530X0530AV
97
98
99X1330X1330
100
101
FN 09MAR-22MAR25
Cell Formulas
RangeFormula
K5:L6K5=FILTER(DATA!$AM$3:$AM$19,NOT(COUNTIF(K11:K111,DATA!$AM$3:$AM$19)),"")
M5M5=FILTER(DATA!$AL$3:$AL$17,NOT(COUNTIF(M11:M111,DATA!$AL$3:$AL$17)),"")
Dynamic array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop 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:T112Expression=AND(COUNTIF($AU$18:$AU$44,G19)=0,COUNTIF(G$19:G$110,G19)>1)textNO
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:T112Cell Valuecontains "EDO-U"textYES
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:T112Cell Valuecontains "OFF-U"textYES
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:T112Expression=OR(COUNTIF(G19,"*BLV*"), COUNTIF(G19,"*CDO*"), COUNTIF(G19,"*SDO*"), COUNTIF(G19,"*PDO*"), COUNTIF(G19,"*TFN*"))textYES
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:T112Expression=OR(COUNTIF(G19,"*A/L*"), COUNTIF(G19,"*BONUS*"), COUNTIF(G19,"*GAZETTE*"), COUNTIF(G19,"*LSL*"), COUNTIF(G19,"*MATLVE*"), COUNTIF(G19,"*PATLVE*"), COUNTIF(G19,"*PHC*"))textYES
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:T112Cell Valuecontains "OFF"textYES
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:T112Cell Valuecontains "EDO"textYES
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:T113Cell Valueending with "?"textNO
G35:T94,G96:T110,G112:T126,G22:T33,G19:T19,G20,H20:T21Cell Valuecontains "AV"textNO
G11:T14,G16:T17,G19:T33,G35:T94,G96:T110,G112:T126,G5:T9,G2:T3Expression=G$3=TODAY()textNO
G11:T14,G16:T17,G19:T33,G35:T94,G96:T110,G112:T126,G5:T9,G2:T3Expression=COLUMN()=CELL("COL")textNO
G5:T5Cellcontains an errortextNO
G2:T3,G11:T14,G16:T17,G19:T33,G35:T94,G96:T110,G112:T126,G5:T9Expression=COUNTIF(DATA!$AV$4:$AV$16,G$3)=1textNO
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:T113Cell Valuecontains "OK"textNO
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:T113Cell Valuecontains "DEC"textNO
Cells with Data Validation
CellAllowCriteria
K25:M25List=DATA!$AF$4:$AF$56
K35:M35List=DATA!$AF$4:$AF$56
K96:M96List=DATA!$AF$4:$AF$56
K99:M99List=DATA!$AF$4:$AF$56
K44:M44List=DATA!$AF$4:$AF$56
K47:M47List=DATA!$AF$4:$AF$56
K50:M50List=DATA!$AF$4:$AF$56
K53:M53List=DATA!$AF$4:$AF$56
K56:M56List=DATA!$AF$4:$AF$56
K59:M59List=DATA!$AF$4:$AF$56
K62:M62List=DATA!$AF$4:$AF$56
K65:M65List=DATA!$AF$4:$AF$56
K68:M68List=DATA!$AF$4:$AF$56
K71:M71List=DATA!$AF$4:$AF$56
K74:M74List=DATA!$AF$4:$AF$56
K77:M77List=DATA!$AF$4:$AF$56
K80:M80List=DATA!$AF$4:$AF$56
K83:M83List=DATA!$AF$4:$AF$56
K86:M86List=DATA!$AF$4:$AF$56
K89:M89List=DATA!$AF$4:$AF$56
K31:M31List=DATA!$AF$4:$AF$56
 
Based on this description, you could conditionally format K96 with the formula:

Excel 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?
 
Upvote 0
Based on this description, you could conditionally format K96 with the formula:

Excel 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
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top