Getting a cell to highlight when a date is entered elsewhere

[damian_r_Home]

Afternoon all.
I think this is a bit of a complex one but I'll do my best to explain bit by bit.

Basically, cell D5 will always need to show the date of the Monday of the current week, K5 will show the date of the next Monday and so on across the spreadsheet for about the next 10 weeks.
What is the formula so that D2 always show the Monday of the current week (with the week beginning on a Monday and finishing on a Sunday)

Now, for the most tricky part I think.
Cell C4 will have a date entered into it and I need the cell which corresponds to that date across the columns to shade a certain colour.
For example, assuming that the dates across star with the current Monday week of 02-03-15 (in D2), if the cell C4 has the date of 10-03-15, cell L3 would need to become shaded.
However, when we enter next week, D2 would show the date of 09-03-15 which means the date of 10-03-15 in C4 would cause E4 to highlight

I find conditional formatting difficult at the best of times so trying to figure this one out is a nightmare for me!!!

Your help in sorting this would be much appreciated.......

Damian

 

[damian_r_Home]

OK, I've figured out some of this myself in a work around method nut can somebody help with getting cell D5 to always show the date of the Monday of the current week please....

 

[DeltaOmega]

Hi,

Not sure if this is the ideal solution, but at least it seems to work.

=Today()-(WEEKDAY(TODAY(),2)-1)

Hope it helps

 

[damian_r_Home]

@DeltaOmega

It works just fine and as such that's the important bit!!

Many thanks

Damian