Highlight cell in column when compared to each value of another column

[Adamahl]

Hey,

I need help with a formula to highlight a cell in a column when comparted to each value in another value. If I have the columns

A B
1 1
1 2
2 3
3 5
4 7
7 8

I want the values in column A to be highlighted if the excist in column B, both of the number 1:s.

Thanks for the help

[Tony Valko]

Try this...

Let's assume the range to highlight is A2:A10.

Select the *entire* range A2:A10 starting from cell A2.
Cell A2 will be the active cell. The active cell is the
one cell in the selected range that is not shaded. The
formula will be relative to the active cell.

Goto the Home tab>Styles>Conditional Formatting>
Manage rules>New rule>Use a formula to determine
which cells to format

Enter this formula in the box below:

=MATCH(A2,B:B,0)

Click the Format button
Select the Fill tab
Select the desired color
OK out

[Adamahl]

Thanks for the answer,

When i try that it only says that the formula contains an error... I had a list from A2-A10 and B2-B10 and copied your formula in the new formatting rule box... Does it work when you type that in to your excel?

[Tony Valko]

You might have to use semi-colons instead of commas in the formula:

=MATCH(A2;B:B;0)

[Fotis1991]

Replace the comma's in Tony Valko's formula with semi colons.