Colour a cell if having a match in adjacent column

**meetvivek72** · 07-07-2011, 07:54 AM

Well, i have a query regarding the excel workbook (attached here). In this i need those values to be highlighted in column A with some color which are also present in column B.

Example:

Set A Set B
Contig 1 Contig 1
Contig 4 Contig 44
Contig 12 Contig 33
Contig 21 Contig 4

thus it should highlight (some color) Contig 1 and Contig 4 in Set A, bcoz these are the only values matching with Set B values.

**sweep** · 07-07-2011, 07:59 AM

Hi,

You can use conditional formatting with the following formula to obtain the required result:

=ISNUMBER(MATCH(A1,B:B,0))

Please see attachment.

**meetvivek72** · 07-07-2011, 08:40 AM

actually the formula provided by u is working fine, but one thing in which cell i hv to use it.....

**sweep** · 07-07-2011, 09:21 AM

It doesn't go in a cell, it has to be used to activate conditional formatting

Select Column A
Go to Format > Conditional Formatting
From the first listbox, choose "Formula Is"
Enter a formula as it would refer to the active cell in the selection (Should be A1)
=ISNUMBER(MATCH(A1,B:B,0))
Click the Format button.
Select the required formatting
Click OK > OK