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

 

[MeaclH]

To apply it easily to multiple columns, leave the absolute reference in the first part of the formula alone, but take the absolute references off the second part, like this:

=AND(COUNTIF($E$26:$E$49,C25)=0,COUNTIF(C25:C92,C25)>1)

You can change the addresses to your actual locations first of course. Then highlight the first column of interest & copy the formats across to your entire range.

Interesting, when I make this change only the first cell has the formatting applied, the lower of the 2 doesn't have formatting applied to it. Ideas?

 

[kevin9999]

Sorry, should have been:

=AND(COUNTIF($E$26:$E$49,C25)=0,COUNTIF(C$25:C$92,C25)>1)

 

[MeaclH]

Sorry, should have been:

=AND(COUNTIF($E$26:$E$49,C25)=0,COUNTIF(C$25:C$92,C25)>1)

BOOM!
Thanks mate, you're a rockstar.
Appreciate it all!

 

[kevin9999]

Glad we got there in the end