Gantt chart - Conditional formatting

[hzhm]

Hi, I have a Gantt chart with conditional formatting (grey fill colour) that calculate on dates. One cell represents 1 week. See example sheet attached. The problem with my formula is that if I enter a date eg 30/9/2013 it fills the correct cell but also includes the previous week.
Can someone have a look at the formula please.
thanks

 

[Hui]

Hzhm

You haven't attached a file?
The Upload File is besides the Post Reply button

 

[hzhm]

file uploaded again.

thanks

 

[SirJB7]

Hi, hzhm!
Try changing the CF condition formula for grayed background to this:
=Y(COLUMNA()-7>=COINCIDIR($D5;$H$4:$FM$4;1);COLUMNA()-7<=COINCIDIR($E5;$H$4:$FM$4;1)) -----> in english: =AND(COLUMN()-7>=MATCH($D5,$H$4:$FM$4,1),COLUMN()-7<=MATCH($E5,$H$4:$FM$4,1))
It considers the whole week grayed even if only 1 day enters on that period.
Regards!

 

[hzhm]

thanks SirJB7, it works. Can the formula be modified so the column range is not limited to $FM$4. I want to auto update the range if more columns are added after FM. Normally I would add more columns to the end by a copy paste formatting and fill series for the dates. Otherwise I see the solution is to just edit the conditional forumula and change fm to the new column.
thanks

 

[hzhm]

SirJB7, I think I have the answer, the formula for the end range is a mixed reference not an absolute: instead of MATCH($E5,$H$4:$FM$4,1)) is would be MATCH($E5,$H$4:FM$4,1)). Correct?

 

[SirJB7]

Hi, hzhm!
I set the formula to the existing columns and to the range that your formulas used. To make it dynamic you should define a dynamic named range as:
=DESREF($H$4;;;1;CONTARA($4:$4)-7) -----> in english: =OFFSET($H$4,,,1,COUNTA($4:$4)-7)
and then use that name in the formulas.
Regards!