highlight cells in sheet 1 based on cells existing in sheet 2

[free2rhyme2k]

Hello,

I have a sheet (sheet1) of around 900 rows with data in columns A-K. Column B contains a unique value.

Sheet2 contains data which needs to be highlighted in sheet1 and each row in identified by a unique value in Column B.

sheet1.PNG
sheet2.png

In sheet 1, I only want to highlight the cell which exists in sheet 2 for the matching reference number in column B. For example, in sheet2, the value 'PM328491CN' exists and the value 'CN' is present in cell c2. I want the cell that contains this value in sheet 1 to be highlighted (cell C6).

So, find the matching reference, the highlight the cell in the row which is present in both sheets.

Any ideas?

[Debraj Roy]

In Sheet 1, use Conditional formatting as

Please Login or Register  to view this content.

and apply same formatting of all B:B in sheet1

Sample CF.xlsx