Formatting if cell value found in range

[MeaclH]

Gday all,

I am looking for a formula that will apply conditonal formatting to a cell if the value in that cell is found elsewhere in a range.
In the sample data below for example, in rows 35,38,41,44 and so on, I would like to see if there are duplicates of any values. The one privso I can't seem to figure out is that I only want it to show duplicates for certain values.
This is a roster so there may be multiple people rostered OFF which I don't want to change the formatting of, only the shifts. I'm happy to use the codes of all 20 odd shifts in the formula if that would make it easier?

Hope that makes sense.
Cheers,
Hayden

DIGITAL ROSTER - VERSION 3.0.xlsm
	N
35	OFF
36
37
38	0530NCC
39
40
41	2130PL/S
42
43
44	1330NCC
45
46
47	OFF
48
49
50	0530PB
51
52
53	OFF
54
55
56	2130PB
57
58
59	OFF
60
61
62	1330PB
63
64
65	OFF
66
67
68	0530PL/S
69
70
71	OFF
72
73
74	OFF
75
76
77	1330BC
78
79
80	2130NCC
81
82
83	1330PL/S
84
85
86	OFF
87
88
89	2130BC
90
91
92	0530BC
93
94
FN 23MAR-05APR25

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G35:T94,G96:T110,G112:T126,G22:T33,G19:T19,G20,H20:T21	Cell Value	contains "SPDO"	text	YES
G35:T94,G96:T110,G112:T126,G22:T33,G19:T19,G20,H20:T21	Cell Value	contains "CPDO"	text	YES
G35:T94,G96:T110,G112:T126,G22:T33,G19:T19,G20,H20:T21	Cell Value	contains "BONUS"	text	NO
G35:T94,G96:T110,G112:T126,G22:T33,G19:T19,G20,H20:T21	Cell Value	contains "MATLVE"	text	NO
G35:T94,G96:T110,G112:T126,G22:T33,G19:T19,G20,H20:T21	Cell Value	contains "PATLVE"	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	ending with "?"	text	NO
G35:T94,G96:T110,G112:T126,G22:T33,G19:T19,G20,H20:T21	Cell Value	contains "GAZETTE"	text	YES
G35:T94,G96:T110,G112:T126,G22:T33,G19:T19,G20,H20:T21	Cell Value	contains "AV"	text	NO
G35:T94,G96:T110,G112:T126,G22:T33,G19:T19,G20,H20:T21	Cell Value	contains "EDO-U"	text	YES
G35:T94,G96:T110,G112:T126,G22:T33,G19:T19,G20,H20:T21	Cell Value	contains "OFF-U"	text	YES
G35:T94,G96:T110,G112:T126,G22:T33,G19:T19,G20,H20:T21	Cell Value	contains "BLV"	text	YES
G35:T94,G96:T110,G112:T126,G22:T33,G19:T19,G20,H20:T21	Cell Value	contains "CTFN"	text	YES
G35:T94,G96:T110,G112:T126,G22:T33,G19:T19,G20,H20:T21	Cell Value	contains "CDO"	text	YES
G35:T94,G96:T110,G112:T126,G22:T33,G19:T19,G20,H20:T21	Cell Value	contains "STFN"	text	YES
G35:T94,G96:T110,G112:T126,G22:T33,G19:T19,G20,H20:T21	Cell Value	contains "SDO"	text	YES
G35:T94,G96:T110,G112:T126,G22:T33,G19:T19,G20,H20:T21	Cell Value	contains "LSL"	text	YES
G35:T94,G96:T110,G112:T126,G22:T33,G19:T19,G20,H20:T21	Cell Value	contains "A/L"	text	YES
G35:T94,G96:T110,G112:T126,G22:T33,G19:T19,G20,H20:T21	Cell Value	contains "PHC"	text	YES
G35:T94,G96:T110,G112:T126,G22:T33,G19:T19,G20,H20:T21	Cell Value	contains "OFF"	text	YES
G35:T94,G96:T110,G112:T126,G22:T33,G19:T19,G20,H20:T21	Cell Value	contains "EDO"	text	YES
G1:T3,G11:T14,G16:T17,G19:T33,G35:T94,G96:T110,G112:T126,G5:T9	Expression	=COLUMN()=CELL("COL")	text	NO
G1:T3,G11:T14,G16:T17,G19:T33,G35:T94,G96:T110,G112:T126,G5:T9	Expression	=G$3=TODAY()	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

 

[coulombevin]

Hi Hayden,

Could you explain more simply the problem so I could wrap my head around and figure a solution?

In Range C25:C92 you want to hilight if the value OFF is present more than once?

if not, could you write the range you want to color, and something like if value x, y, z then background green, if i,j,k then font red, ...

Thanks,

Vincent

 

[MeaclH]

Hi Hayden,

Could you explain more simply the problem so I could wrap my head around and figure a solution?

In Range C25:C92 you want to hilight if the value OFF is present more than once?

if not, could you write the range you want to color, and something like if value x, y, z then background green, if i,j,k then font red, ...

Thanks,

Vincent

Hi Vincent,

Thanks for taking the time to reply!

Essentially there are around 20 odd shift codes that get used day to do.
In range C25:C92 I want to have a conditional formatting rule that will apply to any shift code that appears more than once.
The hang up is that I don’t want the rule to check for multiple occurrences off particular codes used for staff on days off.
I’m wondering if the rule will need to have each shift code in it or there is another way to have it trigger.
The shift codes are the start time in 24 hour clock followed by letter identifier.
ie. 0530BC, 1330NCC.

I want to be alerted if there is a double up of shifts allocated only.

Hope this explains better.

The formatting is undecided, maybe a red background, white font.

Cheers
Hayden

 

[kevin9999]

I'm still not clear on exactly what you want. If I interpret your requirement of formatting cells in the range C25:C92 where the code is a duplicate but not "OFF", then is this what you want:

Book1
	C
25	OFF
26
27
28	0530NCC
29
30
31	2130PL/S
32
33
34	1330NCC
35	1330NCC
36
37	OFF
38
39
40	0530PB
41
42
43	OFF
44
45
46	2130PB
47
48
49	OFF
50
51
52	1330PB
53
54
55	OFF
56
57
58	0530PL/S
59	0530PL/S
60
61	OFF
62
63
64	OFF
65
66
67	1330BC
68
69
70	2130NCC
71
72	0530PL/S
73	1330PL/S
74
75
76	OFF
77
78
79	2130BC
80
81
82	0530BC
83
84
85
86
87
88
89
90
91
92
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C25:C92	Expression	=AND(C25<>"OFF",COUNTIF($C$25:$C$92,C25)>1)	text	YES

 

[MeaclH]

I'm still not clear on exactly what you want. If I interpret your requirement of formatting cells in the range C25:C92 where the code is a duplicate but not "OFF", then is this what you want:

Book1
	C
25	OFF
26
27
28	0530NCC
29
30
31	2130PL/S
32
33
34	1330NCC
35	1330NCC
36
37	OFF
38
39
40	0530PB
41
42
43	OFF
44
45
46	2130PB
47
48
49	OFF
50
51
52	1330PB
53
54
55	OFF
56
57
58	0530PL/S
59	0530PL/S
60
61	OFF
62
63
64	OFF
65
66
67	1330BC
68
69
70	2130NCC
71
72	0530PL/S
73	1330PL/S
74
75
76	OFF
77
78
79	2130BC
80
81
82	0530BC
83
84
85
86
87
88
89
90
91
92
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C25:C92	Expression	=AND(C25<>"OFF",COUNTIF($C$25:$C$92,C25)>1)	text	YES

Hi Kevin, yes this is pretty much spot on. Can I add more values that aren’t to be counted as multiples? I assume just separated by a comma?

Thanks so much for your help!

Cheers
Hayden

 

[kevin9999]

How many more values do you want to exclude? Could you list them?

 

[MeaclH]

there could be 20 odd…

How many more values do you want to exclude? Could you list them?

 

[kevin9999]

Could you list them on a table on the same sheet? I think the CF formula could be made a lot simpler if you could.

 

[zero269]

Hi @MeaclH

I am looking for a formula that will apply conditonal formatting to a cell if the value in that cell is found elsewhere in a range.

I see you're in good hands with Kevin taking care of your request regarding CFRs to address duplicates with custom exclusions.

I was looking at your request earlier and noticed you have a lot of CFR's; many using identical formatting.
I use CFR's alot in some cases, and wanted to share some advice on how to combine some of them... if interested.

For example, you have some Orange Fill being applied to the same ranges that contain either of the following text: BLV, CDO, SDO, CPDO, SPDO, CTFN, STFN
All of these could be combined using the COUNTIF function. I've used this many times in several workbooks.

Below I'm providing both a Long Version and a Short Version for the following: Orange Fill, Green Fille, Yellow Fill and No Formatting.
You can view and grab the formulas from the Mini Sheets below...

Note: I'm using wildcards (*) becasue that's what "contains" does by default.
CONSIDER: If you use...

• PDO instead of CPDO & SPDO, will this wind up formatting anything else that shouldn't be? If not, then I would use the substitute.
• TFN instead of CTFN & STFN...?

Criteria used for both versions:

• Orange Fill (Long Version) using: BLV, CDO, SDO, CPDO, SPDO, CTFN, STFN
• Orange Fill (Short Version) using: BLV, CDO, SDO, ~PDO,~TFN
• Green Fill (Long Version) using: EDO, EDO-U
• Green Fill (Short Version) using just EDO considering it will format both criteria anyway.
• Yellow Fill (Long Version) using: OFF, OFF-U
• Yellow Fill (Short Version) using just OFF considering it will format both criteria anyway.
• No Formatting (Long Version) using: A/L, BONUS, GAZETTE, LSL, MATLVE, PATLVE, PHC
• No Formatting (Short Version) using: nothing

Personally, I don't think you need to use the No Formatting CFRs. They are not being used to apply any conditional formatting and appear to have no effect on the cells that contain those various string values. Is there some other formatting taking place where these are removing formatting? Just curious.

Regarding Ranges the formatting is being applied to:

You technically have four different ranges that your formatting is being applied to for the CFR's I've mentioned above.
I also noticed you have a break in your top range (image). Those marching ants show 4 seperate selections... thus leaving G21 out of the range.

Your current range:

=$G$35:$T$94,$G$96:$T$110,$G$112:$T$126,$G$22:$T$33,$G$19:$T19,$G$20,$H$20:$T$21

The fixed range:

=$G$19:$T$33,$G$35:$T$94,$G$96:$T$110,$G$112:$T$126

To test the CFR's I'm proposing, I created some sample data using a list of your criteria, and applying it to your top range randomly using the following formula.
W19:W36 is a list of your criteria.

=INDEX($W$19:$W$36,RANDBETWEEN(1,ROWS($W$19:$W$36)),1)

This 1st Example is using all of the Long Versions of the CFRs:
BLV, CDO, SDO, CPDO, SPDO, CTFN, STFN | EDO, EDO-U | OFF, OFF-U | A/L, BONUS, GAZETTE, LSL, MATLVE, PATLVE, PHC

VBA Testing.xlsm
	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
19	OFF-U	LSL	STFN	BLV	BLV	OFF-U	CPDO	SPDO	A/L	OFF-U	OFF	OFF-U	SPDO	PATLVE
20	SPDO	BLV	OFF	EDO-U	SDO	BLV	A/L	BLV	OFF-U	OFF	EDO-U	STFN	EDO-U	SPDO
21	OFF-U	A/L	BLV	CTFN	EDO	SPDO	EDO	MATLVE	BLV	BLV	BONUS	PHC	A/L	EDO
22	CDO	CDO	A/L	CTFN	LSL	BONUS	A/L	CTFN	LSL	OFF-U	MATLVE	OFF-U	SPDO	STFN
23	BONUS	CPDO	EDO	BLV	BONUS	LSL	CTFN	OFF	LSL	PATLVE	EDO	SPDO	CDO	EDO
24	CDO	PATLVE	MATLVE	SPDO	PHC	SPDO	EDO	A/L	EDO-U	OFF-U	SDO	CPDO	EDO-U	LSL
25	PHC	MATLVE	CPDO	CDO	LSL	CPDO	CPDO	LSL	CPDO	BLV	SPDO	BONUS	OFF	PHC
26	PATLVE	OFF	A/L	CTFN	STFN	BLV	OFF	BONUS	MATLVE	MATLVE	BLV	A/L	CDO	A/L
27	STFN	EDO-U	CPDO	OFF-U	CTFN	MATLVE	OFF-U	LSL	EDO-U	EDO-U	PATLVE	OFF	PHC	OFF-U
28	SDO	CTFN	STFN	A/L	GAZETTE	PATLVE	OFF-U	STFN	GAZETTE	MATLVE	CTFN	CDO	EDO	CTFN
29	SPDO	EDO	CTFN	GAZETTE	CPDO	EDO-U	SDO	PATLVE	CDO	GAZETTE	CPDO	A/L	MATLVE	OFF-U
30	GAZETTE	STFN	SPDO	CTFN	CTFN	CPDO	GAZETTE	PHC	PHC	LSL	PATLVE	BONUS	EDO-U	A/L
31	EDO	MATLVE	BONUS	PHC	MATLVE	BLV	CPDO	BONUS	SDO	BONUS	OFF	STFN	EDO-U	CDO
32	BLV	LSL	PATLVE	CDO	CPDO	LSL	GAZETTE	BLV	LSL	LSL	STFN	SDO	LSL	LSL
33	EDO-U	GAZETTE	CTFN	CPDO	GAZETTE	CTFN	MATLVE	STFN	CTFN	CPDO	SPDO	LSL	MATLVE	STFN
CountUnique

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G19:T33,G35:T94,G96:T110,G112:T126	Expression	=OR(COUNTIF(G19,"*BLV*"), COUNTIF(G19,"*CDO*"), COUNTIF(G19,"*SDO*"), COUNTIF(G19,"*PDO*"), COUNTIF(G19,"*TFN*"))	text	NO
G19:T33,G35:T94,G96:T110,G112:T126	Expression	=OR(COUNTIF(G19,"*EDO*"), COUNTIF(G19,"*EDO-U*"))	text	NO
G19:T33,G35:T94,G96:T110,G112:T126	Expression	=OR(COUNTIF(G19,"*OFF*"), COUNTIF(G19,"*OFF-U*"))	text	NO
G19:T33,G35:T94,G96:T110,G112:T126	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	NO

This 2nd Example is using all of the Short Versions of the CFRs:
BLV, CDO, SDO, PDO, TFN | EDO | OFF | nothing

VBA Testing.xlsm
	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
19	OFF-U	LSL	STFN	BLV	BLV	OFF-U	CPDO	SPDO	A/L	OFF-U	OFF	OFF-U	SPDO	PATLVE
20	SPDO	BLV	OFF	EDO-U	SDO	BLV	A/L	BLV	OFF-U	OFF	EDO-U	STFN	EDO-U	SPDO
21	OFF-U	A/L	BLV	CTFN	EDO	SPDO	EDO	MATLVE	BLV	BLV	BONUS	PHC	A/L	EDO
22	CDO	CDO	A/L	CTFN	LSL	BONUS	A/L	CTFN	LSL	OFF-U	MATLVE	OFF-U	SPDO	STFN
23	BONUS	CPDO	EDO	BLV	BONUS	LSL	CTFN	OFF	LSL	PATLVE	EDO	SPDO	CDO	EDO
24	CDO	PATLVE	MATLVE	SPDO	PHC	SPDO	EDO	A/L	EDO-U	OFF-U	SDO	CPDO	EDO-U	LSL
25	PHC	MATLVE	CPDO	CDO	LSL	CPDO	CPDO	LSL	CPDO	BLV	SPDO	BONUS	OFF	PHC
26	PATLVE	OFF	A/L	CTFN	STFN	BLV	OFF	BONUS	MATLVE	MATLVE	BLV	A/L	CDO	A/L
27	STFN	EDO-U	CPDO	OFF-U	CTFN	MATLVE	OFF-U	LSL	EDO-U	EDO-U	PATLVE	OFF	PHC	OFF-U
28	SDO	CTFN	STFN	A/L	GAZETTE	PATLVE	OFF-U	STFN	GAZETTE	MATLVE	CTFN	CDO	EDO	CTFN
29	SPDO	EDO	CTFN	GAZETTE	CPDO	EDO-U	SDO	PATLVE	CDO	GAZETTE	CPDO	A/L	MATLVE	OFF-U
30	GAZETTE	STFN	SPDO	CTFN	CTFN	CPDO	GAZETTE	PHC	PHC	LSL	PATLVE	BONUS	EDO-U	A/L
31	EDO	MATLVE	BONUS	PHC	MATLVE	BLV	CPDO	BONUS	SDO	BONUS	OFF	STFN	EDO-U	CDO
32	BLV	LSL	PATLVE	CDO	CPDO	LSL	GAZETTE	BLV	LSL	LSL	STFN	SDO	LSL	LSL
33	EDO-U	GAZETTE	CTFN	CPDO	GAZETTE	CTFN	MATLVE	STFN	CTFN	CPDO	SPDO	LSL	MATLVE	STFN
CountUnique

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G19:T33,G35:T94,G96:T110,G112:T126	Expression	=OR(COUNTIF(G19,"*BLV*"), COUNTIF(G19,"*CDO*"), COUNTIF(G19,"*SDO*"), COUNTIF(G19,"*PDO*"), COUNTIF(G19,"*TFN*"))	text	NO
G19:T33,G35:T94,G96:T110,G112:T126	Expression	=COUNTIF(G19,"*EDO*")	text	NO
G19:T33,G35:T94,G96:T110,G112:T126	Expression	=COUNTIF(G19,"*OFF*")	text	NO

 

[kevin9999]

Could you list them on a table on the same sheet? I think the CF formula could be made a lot simpler if you could.