Conditional formatting - dates in one column, merged cells in other columns

[dk73]

Below is a mock-up of something similar I am trying to achieve. CF has been applied to column A only, columns B and C have manual formatting.
What I want in the columns B and C is:
- if all of the 7 dates next to the merged cell is before today, format as grey fill
- if any of the 7 dates next to the merged cells equals today, format as green fill

Before anyone suggests unmerging the cells in columns B and C, they each have a few lines of data in them so ideally I want to leave the merged cells as is.

So, is it possible to use CF to get the formatting as below?

 

[James006]

Hi,

Are all your Shifts consisting of 7 merged rows ?

To be tested :

=OFFSET(B9,6,-(COLUMN()-1))<=TODAY()

 

[dk73]

Most are as they are for one week, although there was exception where rather than 7 and 7, two were 13 and 1. But I can probably change them to 7 and 7 and add a note or comment in the cells.

 

[James006]

Have you tested the formula ?

 

[dk73]

I have decided to change the way I am presenting the data, I'm even doing away with merged cells for this issue!

 

[James006]

I have decided to change the way I am presenting the data, I'm even doing away with merged cells for this issue!

Does it also mean you got rid of your problem ... or is there still a question pending ?

 

[James006]

Glad to see you could fix your initial problem