Giving an excel file with 2 columns:

A: 5000 numbers (some repeated) B: 525 unique numbers

I want to know if for each number in A there is a instance of that number in B.

Meaning: for A1, is this value in column B?, if so highlight cell in green, otherwise, in RED.

I actually got the duplicates by using in column C this:

```
=COUNTIF($B$2:$B$525,A2)
```

This gives me 0 as False and 1 as TRUE, so far so good!

Now what I want to know is how to build a conditional formatting .

- I go to Edit Formatting Rule
- Use a formula to determine wich cells to format
RULE:

`=COUNTIF(B2:B9999,A15)`

A15 because cell A15 is true, A2 to A14 is false.

Applies to: A2:A9999

However this highlights random cells.

So I think I did the hardest part but I am stuck in what seems to be the easiest.

Any guidance?

REALLYmisleading. It looks like you've solved the problem of finding duplicates, and your question is about conditional formatting. (2) Have you tried making the conditional formatting formula the same as the one that works, with dollar signs in the same places? (3) If that doesn't solve it, quantify "random". Do they change when you refresh? Can you reproduce the problem with 10`A`

values and 5`B`

values? If so, post it. Don't post screenshots, type in your data, as was done here and here. – Scott Jun 14 '15 at 5:52