Problem

[damanwitdaplan]

I also need to make sure that '10', '20', '30', '-5', '-10', '-15' only
appears once in the cells B12:16, B26:31. Any combonation of numbers (i.e.
'10' in B14 and '-15' in B26) also needs to be highlighted. Thanks again for
your help!

 

[Sandy Mann]

Well, it works for me. I cannot think of anything that would cause what you
are getting if all your cells are referencing the same list.

I'm just off to bed now, if you want, you can send me a copy of your sheet
and I will take a look tomorrow.

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[damanwitdaplan]

I also have the sum of B12:B16 in B17.

 

[Sandy Mann]

Just to complete the thread for the archives, the OP wanted conditional
formatting for any duplicate value in a row or any two of the values he gave
below in a row.

Two suggestions, with 2 conditional formats:

Any duplicate value:
=AND(B12<>"",COUNTIF($B12:$U12,B12)>1)

Any two of the values below:
=AND(B12<>"",SUMPRODUCT(--(B12=$AA$1:$AA$7))=1,SUMPRODUCT(--($B12:$U12=$AA$1:$AA$7))>1)

Or both conditions in one formula:
=AND(B12<>"",OR(COUNTIF($B12:$U12,B12)>1,AND(SUMPRODUCT(--(B12=$AA$1:$AA$6))=1,SUMPRODUCT(--($B12:$U12=$AA$1:$AA$6))>1)))

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk