In Gantt timeline, change date cell color when status changes for that date range

%3CLINGO-SUB%20id%3D%22lingo-sub-3269926%22%20slang%3D%22en-US%22%3EIn%20Gantt%20timeline%2C%20change%20date%20cell%20color%20when%20status%20changes%20for%20that%20date%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3269926%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EI%20have%20a%20Gantt%20chart%20project%20plan%20with%20fields%20Start%20Date%2C%20End%20Date%20%26amp%3B%20Status%20(To%20Do%2C%20In%20Progress%20%26amp%3B%20Done).%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EWhenever%20i%20change%20the%20status%2C%20i%20need%20the%20colour%20of%20the%20selected%20date%20range%20cells%20to%20be%20changed%20in%20the%20timeline%20section.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EFor%20example%2C%20as%20shown%20in%20the%20attachent%2C%20when%20i%20change%20the%20status%20for%20the%20start%20date%20-%20end%20date%20range%2025-Jun-21%20-%2006-Jul-21%2C%20the%20corresponding%20date%20cells%20in%20the%20timeline%20portion%20should%20change%20to%20a%20different%20color.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22GanttDateColourChange.jpg%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F359451i2F073D67C5D0B461%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22GanttDateColourChange.jpg%22%20alt%3D%22GanttDateColourChange.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EHope%20someone%20can%20help%20me%20out.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3269926%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3270168%22%20slang%3D%22en-US%22%3ERe%3A%20In%20Gantt%20timeline%2C%20change%20date%20cell%20color%20when%20status%20changes%20for%20that%20date%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3270168%22%20slang%3D%22en-US%22%3EThank%20you!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3270157%22%20slang%3D%22en-US%22%3ERe%3A%20In%20Gantt%20timeline%2C%20change%20date%20cell%20color%20when%20status%20changes%20for%20that%20date%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3270157%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1346353%22%20target%3D%22_blank%22%3E%40arunnairs%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EClick%20in%20cell%20K5.%3C%2FP%3E%0A%3CP%3EOn%20the%20Home%20tab%20of%20the%20ribbon%2C%20click%20Conditional%20Formatting%20%26gt%3B%20Manage%20Rules...%3C%2FP%3E%0A%3CP%3EYou'll%20see%20rules%20for%20green%2C%20yellow%20and%20red.%3C%2FP%3E%0A%3CP%3ESelect%20one%20of%20these%20rules%20and%20click%20Edit...%20to%20see%20the%20formula%20I%20used.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3270131%22%20slang%3D%22en-US%22%3ERe%3A%20In%20Gantt%20timeline%2C%20change%20date%20cell%20color%20when%20status%20changes%20for%20that%20date%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3270131%22%20slang%3D%22en-US%22%3EThank%20you%20Hans%20!!%20This%20is%20what%20i%20wanted.%3CBR%20%2F%3E%3CBR%20%2F%3ECould%20you%20please%20tell%20the%20formula%20or%20condition%20used%3F%3CBR%20%2F%3E%3CBR%20%2F%3ERegards%3CBR%20%2F%3EArun%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3270091%22%20slang%3D%22en-US%22%3ERe%3A%20In%20Gantt%20timeline%2C%20change%20date%20cell%20color%20when%20status%20changes%20for%20that%20date%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3270091%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1346353%22%20target%3D%22_blank%22%3E%40arunnairs%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESee%20the%20attached%20version.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3270011%22%20slang%3D%22en-US%22%3ERe%3A%20In%20Gantt%20timeline%2C%20change%20date%20cell%20color%20when%20status%20changes%20for%20that%20date%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3270011%22%20slang%3D%22en-US%22%3EAs%20of%20now%2C%20depending%20on%20the%20start%20date%20and%20end%20date%20selection%2C%20the%20date%20cells%20in%20timeline%20portion%20gets%20filled%20with%20a%20color.%20(Currently%20violet%20is%20given)%3CBR%20%2F%3E%3CBR%20%2F%3EThe%20status%20column%20has%203%20values%20namely%20To%20Do%2C%20In%20Progress%20and%20Done.%3CBR%20%2F%3EWhenever%20I%20change%20the%20status%2C%20i%20need%20the%20color%20of%20the%20timeline%20cells%20corresponding%20to%20the%20start%20date%20%26amp%3B%20end%20date%20should%20change.%3CBR%20%2F%3EColors%20can%20be%20chosen%20as%20follows%3A%3CBR%20%2F%3ETo%20Do%20-%20Red%3CBR%20%2F%3EIn%20Progress%20-%20Yellow%3CBR%20%2F%3EDone%20-%20Green.%3CBR%20%2F%3E%3CBR%20%2F%3EHope%20this%20makes%20sense.%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2F1drv.ms%2Fx%2Fs!AuCot5kQYYNji291AKYoVnt__Grc%3Fe%3Dg2wac2%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2F1drv.ms%2Fx%2Fs!AuCot5kQYYNji291AKYoVnt__Grc%3Fe%3Dg2wac2%3C%2FA%3E%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3269962%22%20slang%3D%22en-US%22%3ERe%3A%20In%20Gantt%20timeline%2C%20change%20date%20cell%20color%20when%20status%20changes%20for%20that%20date%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3269962%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1346353%22%20target%3D%22_blank%22%3E%40arunnairs%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPlease%20attach%20a%20small%20sample%20workbook%2C%20or%20make%20it%20available%20through%20OneDrive%2C%20Google%20Drive%2C%20Dropbox%20or%20similar.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I have a Gantt chart project plan with fields Start Date, End Date & Status (To Do, In Progress & Done).
Whenever i change the status, i need the colour of the selected date range cells to be changed in the timeline section.
For example, as shown in the attachent, when i change the status for the start date - end date range 25-Jun-21 - 06-Jul-21, the corresponding date cells in the timeline portion should change to a different color.

GanttDateColourChange.jpg

Hope someone can help me out.

6 Replies

@arunnairs 

Please attach a small sample workbook, or make it available through OneDrive, Google Drive, Dropbox or similar.

As of now, depending on the start date and end date selection, the date cells in timeline portion gets filled with a color. (Currently violet is given)

The status column has 3 values namely To Do, In Progress and Done.
Whenever I change the status, i need the color of the timeline cells corresponding to the start date & end date should change.
Colors can be chosen as follows:
To Do - Red
In Progress - Yellow
Done - Green.

Hope this makes sense.


https://1drv.ms/x/s!AuCot5kQYYNji291AKYoVnt__Grc?e=g2wac2

@arunnairs 

See the attached version.

Thank you Hans !! This is what i wanted.

Could you please tell the formula or condition used?

Regards
Arun

@arunnairs 

Click in cell K5.

On the Home tab of the ribbon, click Conditional Formatting > Manage Rules...

You'll see rules for green, yellow and red.

Select one of these rules and click Edit... to see the formula I used.