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

[TABLE="class: grid, width: 128"]
<colgroup><col span="2"></colgroup><tbody>[TR]
[TD]Event 1[/TD]
[TD]Teal[/TD]
[/TR]
[TR]
[TD]Event 2[/TD]
[TD]Blue[/TD]
[/TR]
[TR]
[TD]Event 3[/TD]
[TD]Red[/TD]
[/TR]
</tbody>[/TABLE]


Sheet 2

[TABLE="class: grid, width: 821"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"]May[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="align: center"]Monday, April 30, 2018[/TD]
[TD="align: center"]Tuesday, May 1, 2018[/TD]
[TD="align: center"]Wednesday, May 2, 2018[/TD]
[TD="align: center"]Thursday, May 3, 2018[/TD]
[TD="align: center"]Friday, May 4, 2018[/TD]
[/TR]
[TR]
[TD="align: center"]Event 1[/TD]
[TD="align: center"] [/TD]
[TD="align: center"]Event 2[/TD]
[TD="align: center"] [/TD]
[TD="align: center"]Event 3[/TD]
[/TR]
[TR]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="align: center"]Monday, May 7, 2018[/TD]
[TD="align: center"]Tuesday, May 8, 2018[/TD]
[TD="align: center"]Wednesday, May 9, 2018[/TD]
[TD="align: center"]Thursday, May 10, 2018[/TD]
[TD="align: center"]Friday, May 11, 2018[/TD]
[/TR]
[TR]
[TD="align: center"]Event 4[/TD]
[TD="align: center"] [/TD]
[TD="align: center"]Event 6[/TD]
[TD="align: center"]Event 7[/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="align: center"]Event 5[/TD]
[TD="align: center"] [/TD]
[TD="align: center"]Event 7[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[/TR]
</tbody>[/TABLE]

 

[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.