SOLVED

Conditional Formatting Not applying to cells

Occasional Contributor

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 daysCF displayed in daysOverview of data in useOverview of data in useConditional Formatting time actuallyConditional Formatting time actuallyConditional formatting for time estimatedConditional formatting for time estimated 

 

 

 

 

 

2 Replies
best response confirmed by Sergei Baklan (MVP)
Solution

@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!