Conditional formatting

Question

I have the following:

I want to format A1:B5 if a value in A1:B5 is equal to a value in D1:D5. No formatting if there are duplicates in A1:B5. In above example only cell A5 should be formatted (not cells with the value 99).

Answer 1

=and(countif($A$1:$B$5,A1)=1,countif($D$1:$D$5,A1)=1)

This does seem to check for "No formatting if there are duplicates in A1:B5" but also seems to check for duplicates in D1:D5.

Why isn't A5 highlighted here:?

I would put forward:

=AND(COUNTIF($A$1:$B$5,A1)=1,COUNTIF($D$1:$D$5,A1)>0)

which yields:

Answer 2

Select A1:B5 and set as conditional format:

=and(countif($A$1:$B$5,A1)=1,countif($D$1:$D$5,A1)=1)

Then, not only A5 but also B3 will be formatted.

Answer 3

Good catch p45cal. Thank you.

Answer 4

Thank you very much aalpha and of course you are right about cell B3.