Forum Discussion
Conditional Formatting Not applying to cells
Hi All,
I am in the process of developing a Gantt Chart and using Conditional Formatting to fill the cells from data in adjacent cells. Unfortunately, if I change the sequence from what would be days (=SEQUENCE(1,26,B3,1)) to weeks (=SEQUENCE(1,26,B3,7)), it does not appear to fill the first correct cell in that sequence unless the Actual Start Date($F9) is equal to the date in the cell. For example, if F9= 4/7/22 and the Date cell K5=4/7/22 it will populate that cell with the conditional formatting, which is a green colour. However, if that date is later in the week up to the weekend (5th,6th,7th,8th) the next cell is highlighted would be the filled with the conditional formatting.
Could someone please advise how when the dates are displayed in weeks (row5) I have all correct cells that meet the condition populate with the CF below it.
Also my CF for the time estimated appear to refer to different cells, but somehow works correctly, should it be a case if its not broken don't fix it?
Thank you in advance, any assistance would be greatly appreciated!
CF displayed in daysOverview of data in useConditional Formatting time actuallyConditional formatting for time estimated
njconsult I did some testing and arrived to a CF formula that works for the weekly actual
view.
=AND(K$5>=$F8-WEEKDAY($F8,2)-1,K$5<=WORKDAY($F8,$G8-1,2))
The one for the daily actual view would be:
=AND(K$5>=$F8,K$5<=WORKDAY($F8,$G8-1,2))
Please see if this indeed works out for you
2 Replies
njconsult I did some testing and arrived to a CF formula that works for the weekly actual
view.
=AND(K$5>=$F8-WEEKDAY($F8,2)-1,K$5<=WORKDAY($F8,$G8-1,2))
The one for the daily actual view would be:
=AND(K$5>=$F8,K$5<=WORKDAY($F8,$G8-1,2))
Please see if this indeed works out for you
- Hi Riny,
Thank you so much this worked!
