Excel 2003 VBA or conditional formatting. Date in one cell needs to format remote cell

Hello everybody,

I have looked hard in the forum for an answer to this, but nothing gets close enough for me to be able to sort the problem on my own. Below is an example of a annual roster on which I'm working. I've only shown a small area, for brevity.

The coloured areas are conditionally formatted. Ivory shows Away, green is for Work, pale blue is a day on which a shift-change may occur, and TD is a Transition Day. The mustard colour is the "base", so I don't need to use up a CF option for those cell-values.

What I'd like to do is collect the date from a cell outside the formatted area, rows 12 and 13 in the extract, and then colour the relevant cell red, as per the example. ie CD has an Expiry date of the 2nd Mar, therefore fill the cell corresponding to the relevant day, F7, in red, I drew a red border round it to show what I mean.

Is it possible to do this with a Conditional Formatting formula, preferable, or is VBA the answer?

Thanks in advance, and any help gratefully accepted

What you want to do is click the first thing in the conditional formatting box, changing
Cell value is
to
Formula is
and in the "formula box" that appears, type an equal sign and a formula that is either true or false, and have the formula refer to cells somewhere else in the worksheet. For example in F7 you could enter as the conditional format formula, while assuming you always have month in column L (1,1,1,2,2,2,3,3,3, etc.),
=$L7*100+F$1>100*$D$12+$E$12
As you see, you would have to construct logic that compares the current cell F7 to the date of C12. I simply assumed you would compare (100 times month) + day for a simple comparison, just to show you how it would work.

Then you could use the pattern or whatever in conditional formatting based on the formula being true.

By the way, a defined range name is VERY preferable to $D$12 in the example above.

Does this give you ideas?