match 2 colums 1

[tonyvee1973]

Hi All
Hopefully a quick one.
I have an Excel workbook with two sheets (Sheet1 and sheet2)
I need to compare data in column A on both sheets and colour the cells in sheet2 if they are found in sheet1.

Hope this makes sense
Thanks

 

[Grayedout]

Do you want the matching cells in each sheet comparing against each other? i.e. if Sheet1!A1 = Sheet2!A1, Sheet1!A2 = Sheet2!A2 etc ?

If so then use Conditional Formatting on the cells in Sheet 2. Select all the cells you want formatting in col A, do Conditional Formatting based on a formula and set the formula to be =$A1=Sheet1!$A1. Set the cell colour to be what you wish and this should work.

Graham

 

[tonyvee1973]

Hiya
sorry, didn't really explain myself well.
I want to look at column A in sheet 2 and if the same value is found anywhere in column A sheet 1 then I want that cell in sheet 2 to be highlighted.
I tried using conditional formatting but cannot use this across two sheets

 

[Grayedout]

Try using the Conditional Formatting as above but change the formula to the following =$A1=VLOOKUP($A1,Sheet1!$A$1:$A$100,1,FALSE) (change the 100 to whatever row you want it to go down to)

This should hopefully work !

 

[tonyvee1973]

This works until there is a break in the sequence then it stops looking and produces #NA

 

[Grayedout]

You mean a break in the data in Sheet2? If so then edit the formula to =IF($A1<>"",$A1=VLOOKUP($A1,Sheet1!$A$1:$A$100,1,FALSE),"") which will check for gaps in the data

 

[tonyvee1973]

Thanks greyedout, worked lovely!
Appreciated

 

[Grayedout]

No problem.

 

[byundt]

Older versions of Excel do not permit you to use references to other worksheets in Conditional Formatting criteria. The workaround for that is to use a named range in place of the reference to the other worksheet.

Step by step instructions:
1. On Sheet1, select column A
2. Click in the Address bar (at far left of formula bar), then type Sheet1.A and hit Enter. This will create a named range "Sheet1.A" that refers to Sheet1!$A:$A.
3. On Sheet2, select column A
4. Open the Conditional Formatting menu item and choose New Rule and then "Use a formula to determine which cells to format"
=MATCH($A1,Sheet1.A,0)
5. Click the Format button, then pick a Fill color

Sample workbook on my SkyDrive that will work in Excel 2003 through Excel 2013
[URL unfurl="true"]http://sdrv.ms/17Vo5d7[/url]

Although I named Sheet1 column A "Sheet1.A" with an embedded dot to show both the sheet name and column, this is not required. You could just as well have called the named range "Tony"

By way of explanation, the MATCH formula will return an error value if the value in Sheet2 column A is not found in Sheet1 column A. Conditional Formatting ignores error values. If a match is found, Conditional Formatting will interpret any number other than 0 as TRUE.

Brad