Conditional Formatting based on VLOOKUP

[rwcg2d]

I need help conditionally formatting cells based upon the text found in a VLOOKUP -

I am creating a calendar that references background data to automatically populate events based on their dates. We are also looking to color the events based upon different criteria (i.e. different teams, different invitees, different countries, etc.)

I am looking to connect Sheet 1 & Sheet 2, in order to conditionally format the cells within Sheet 2 with the colors listed in Sheet 1 to the corresponding Event. NOTE: Event colors need to be variable as Event 1 may be Teal for one view, but Orange for another.

Please help

Sheet 1

Event 1	Teal
Event 2	Blue
Event 3	Red

<colgroup><col span="2"></colgroup><tbody>
</tbody>

Sheet 2

		May
Monday, April 30, 2018	Tuesday, May 1, 2018	Wednesday, May 2, 2018	Thursday, May 3, 2018	Friday, May 4, 2018
Event 1		Event 2		Event 3
Monday, May 7, 2018	Tuesday, May 8, 2018	Wednesday, May 9, 2018	Thursday, May 10, 2018	Friday, May 11, 2018
Event 4		Event 6	Event 7
Event 5		Event 7

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[u4carson]

I think the easiest way would be to select all cells on Worksheet 2 and create a conditional formatting rule that correlates the appropriate color to the event on worksheet 1. This way if you change the event name on sheet 1 the appropriate color is still associated with that value on sheet 2.

Step 1 - Select All on Sheet 1
Step 2 - Select Conditional Formatting under Home > Styles
Step 3 - Use a Formula to Determine Which Cells to Format
Step 4 - Select the Event Name on Page 1 to populate the formula under Edit Rule Description (i.e. =Sheet1!$A$1)
Step 5 - Choose the Color you want associated with that event
Step 6 - Repeat for All colors

 

[rwcg2d]

The issue with this is that we need the colors to change based upon different criteria, not just the event name. For example, Event 1 may be Teal when Team 1 looks at their calendar, but Orange when Country 1 looks at it.