How to highlight cells from one sheet in another?

[grim72]

I've a spreadsheet with 2 separate worksheets.

Sheet 1 shows a full list of products
Sheet 2 shows a list of products I intend to delete.

Both sheets have the sames column layout. A is product code, B is Product Description (some products are duplicated several times in Sheet 1).

How do I highlight any product codes on sheet 1 that are included in sheet 2?

See attached spreadsheet for eg.

I want the code 11234A from Sheet2 (A2) to be highlighted in Sheet1 (A2 & A5). ! will have approximately 7000 product lines on Sheet1 and approximately 600 in Sheet2

In an ideal world I'd like the cells to be highlighted in colour so I can sort them by the cell colour?

ExcelFirum Sample.xlsx

[Fotis1991]

In CF rules use this formula

Please Login or Register  to view this content.

How can you use Conditional Formating.

--In Excel 2007 and 2010, Conditional Formatting is in the Styles group on the Home tab. In Excel 2003, Conditional Formatting is on the Format menu. See here how you can work using CF.

[shukla.ankur281190]

Select the whole range on Sheet2 where you need conditional formatting and click Atl O D and put =MATCH(TRUE,IF(Sheet1!$B:$B=Sheet2!$B2,Sheet1!$A:$A=Sheet2!$A2,ROW(1:1)),0) formula and made formatting as you want.