Conditional Formatting Cell Colour Based on Another Cells Colour

Hi folks,

I'm trying to create a script which automatically formats a cells colour, using another cells colour as the reference. I've attached a sample dataset; in summary:

Sheet2 shows a list of peoples names in column A, and a cell colour for that person in column B.

Sheet1 has a range of data, and I would like each cell to be formatted based upon the corresponding cell colour in Sheet2.

Any help would be greatly appreciated!

Thanks,,

Adam

Thanks for your quick reply - very helpful indeed.

When expanding the range on Sheet1, this formula formats any cells with no content within that range with a white background - do you have any idea why that's happening and how to prevent it?

Thanks again!

Adam

Thanks for your help!

Apologies - hopefully one last question...

If I deleted the cell text in Sheet1, how can I undo the cell formatting automatically with VBA?

Thanks

Thanks again - really great support!