Hi,
You've tried conditional formatting & say that 3 conditions is not
enough, but if you have used all 3 conditions to solve this request -
then there will be enough as this can be done with a single condition
in conditional formatting, ie:
=ISNUMBER(MATCH(B2,$A$2:$A$7,0))

However, if you are already using the 3 conditions for other
formatting, have a look at (& the linked pages):
http://www.contextures.com/xladvfilter01.html

hth
Rob Brockett
NZ
Always learning & the best way to learn is to experience...

Hi RedDragon,

The problem you've described accounts for only one condition, so Conditional
Formatting is a candidate.

Rob's formula (for cell B2) uses the MATCH function to test whether the
value in the cell being tested is found in the test range (A2:A7), whilst
the 0 in the function specifies that an exact match is required. When the
MATCH function gets a 'hit' it returns the offset to the first match and,
the 0 causes it to return 'N/A' if there is no match. The ISNUMBER function
then tests the MATCH function's results for the presence of any number, and
returns 'TRUE' if MATCH has returned a number. Inserted into a Conditional
Formatting formula dialogue box, the 'TRUE' result then drives the
formatting.

If you place Rob's formula in cell C2, instead of using it as a conditional
formatting formula, and copy it down, you'll get an idea of what it's doing.

Cheers