Compare data in multiple columns to find duplicates & conditionally highlight

hi all,

I am attempting to use conditional highlighting to highlight duplicate values in a range of data in excel 2007.

Here is exactly what I want:
Column A is a function of a 'toggle switch'. So if I change conditions of the toggle switch the values in column A change.

Columns B through N have static values in them. Some columns have duplicate values within them (i.e. column B has two entries for 'iphone')

I want cells within each column (B:N) to highlight when they match a value in column A.

Example:
Column A
iphone
Blackberry
Nokia
Column B
motorola
Ericsson
Samsung
Column C
LG
iPhone
Blackberry
Sanyo

Ideally, this formula would be backwards compatible with excel 2003.

I'm not familiar with VB so I need it to be a function.

I uploaded an example file to show you what I am looking to do.
In the worksheet called 'stage summary', i have it set up as follows:

Range A6:A150: values are a function of the cell A2 which can be toggled to one of several different options. (example: select 'unclassified' from the drop-down menu in A2 and you will have only 1 value in range A6:A150. If you select all, you'll have 132 entries).
Array B6:M150 are autopopulated cells taken from the worksheet 'Targets'. The cells do not change in response to changes in cell A2.

What I want, is for cells in the array B6:M150 to highlight red when they are duplicates of values in the range A6:A150.

PROBLEM: Some values appear more than once in array B6:M150 (example: Exa77 appears across several different stages and twice within one stage). I don't want these values highlighted unless they match a value in column A.

Thanks very much.

Originally Posted by JBeaucaire

Change your Conditional Formatting formula for B6 to:

=ISNUMBER(MATCH(B6,$A:$A, 0))

Then copy that format to B6:M150 using the Format Painter.

Wow! This appears to work great.
You guys truly are Excel geniuses! I really appreciate your help.