Hi Joshua,
you need to put something in each cell which will trigger the conditional formatting, and you need to have conditional formatting set up in each cell (3 conditions, as you have 3 event types). So, let's choose a cell for which we have an entry in the Event_Data sheet, i.e. 12/09/2013, so we can check on progress as we introduce those features. On the Year_View sheet choose 2013 and select cell F40 and put this formula in the cell:
It should display "Show", as that is the event type for that date. You can un-bolden the cell if you wish, but don't worry about seeing the text for now, as when we set up the CF we will use a foreground colour that is the same as the background colour so it will appear to be empty, but coloured. You can copy that formula if you wish to cells AC42 (under 18/12/2013) and V42 (under 21/11/2013) as those are other cells for which we have dates in the Event_data sheet, and so you should see "Conference" and "Experience BM" in those cells respectively, and if you copy it to any other cell it should return a blank (so the formula is doing what we want it to do).
Select F40 again and click on Conditional Formatting | New Rule and click on the bottom choice - Use a formula ... etc. In the box below enter this formula:
=F40="Show"
then click the Format button, choose the Fill tab and choose your colour (e.g. mid-range orange), then click the Font tab, click on the drop-down for Color (sic) and pick the same colour as for the background, then click OK, and OK again and that cell should now show the orange (and you can't see the text).
You now need to repeat that exercise two more times for the other event types, i.e. select F40, click Conditional Formatting | New Rule | Use a formula ..., enter this formula:
=F40="Conference"
Click Format | Fill tab | choose colour (e.g. mid-range purple), click on Font | Color drop-down | choose same colour, then OK and OK. Repeat one more time using the formula:
=F40="Experience BM"
and choose a different colour (e.g. mid-range green) for both foreground and background.
Now, you can copy that cell under all the dates, including all cells that can be dates in different years. By that I mean that if you look at December 2013, the 1st is a Sunday, and so the 30th and 31st can appear as "orphan" days on a row of their own (meaning that a 31-day month can span six weeks using 2 days of the 6th week, and a 30-day month can use the first day of that 6th week, depending which day the first of the month occurs on). So, it's probably best to copy that cell F40 into C6:I6, K6:Q6, S6:Y6 and AA6:AG6, and then you can copy that row into the cells under all the dates on alternate rows.
That should give you the colour display you were seeking, and you can test it out by putting some more event types and dates in the Event_data sheet.
Hope this helps.
Pete
Bookmarks