Conditional Formatting based on a Lookup

**LEEPRIESTENATOR** · 10-07-2014, 02:48 AM

Hey fellas,

Been trying this for 2 straight weeks with no luck.

I've attached a sheet that gives you an idea of what needs to be achieved.

Sheet1: Main Sheet has columns with every date in a given month in the first row. The second row has the day of the week in DDD format.

Sheet2: Customer Info has customer preferences indicating which days of the week they prefer a vegan or eggetarian diet.

When inputting the customer's name and phone number in the first sheet, I need conditional formatting to highlight cells under the weekdays in the colors based on the preferences provided in the customer sheet.

For example, if John prefers an egg based diet on Monday's, every Monday cell in the same row must be highlighted in yellow. Same for Vegan but in Green.

The cells will contain the name of the dishes that customers will be sent. Each dish is prefixed with E., V. or N. depending on whether it's EGG, VEG or NONVEG based. In case a dish is entered in to a cell that has restrictions on it, (ex. N. Teriyaki in a cell designated as an EGG Day), the text should turn RED.

I hope this can be achieved with conditional formatting. I don't mind VBA, provided it's efficient and not resource hungry as this sheet will contain a lot of data.

Thanks