my gantt chart bar unable to change when i change the start and end date

%3CLINGO-SUB%20id%3D%22lingo-sub-1429243%22%20slang%3D%22en-US%22%3Emy%20gantt%20chart%20bar%20unable%20to%20change%20when%20i%20change%20the%20start%20and%20end%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1429243%22%20slang%3D%22en-US%22%3E%3CP%3EHI%20%2C%3C%2FP%3E%3CP%3Ei%20need%20assistant%20on%20the%20issue%20on%20my%20gantt%20chart%20bar%20unable%20to%20change%20when%20i%20change%20the%20start%20and%20end%20date%20as%20per%20attachment.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1429243%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1430225%22%20slang%3D%22en-US%22%3ERe%3A%20my%20gantt%20chart%20bar%20unable%20to%20change%20when%20i%20change%20the%20start%20and%20end%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1430225%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F684597%22%20target%3D%22_blank%22%3E%40sootheng%3C%2FA%3E%26nbsp%3BI%20am%20not%20quite%20sure%20how%20you%20currently%20generate%20your%20Gantt%20chart%3F%20Did%20you%20do%20it%20by%20manually%20color%20the%20cells%3F%3C%2FP%3E%3CP%3EPlease%20see%20the%20attached%20sample%20workbook.%20I%20only%20present%20the%20suggested%20solution%20to%20Developmental%20section%20(row%208%20to%2015)%20and%20Implementation%20section%20(row%2020%20to%2026).%20My%20solution%20is%20very%20basic%3A%3C%2FP%3E%3CP%3E1.%20Add%20a%20formula%20in%20each%20cell%20from%20column%20H%20to%20the%20right-most%20column%3A%26nbsp%3B%3DIFERROR(IF(AND(H%245%26gt%3B%3D%24D8%2CH%245%26lt%3B%3D%24E8)%2C1%2C0)%2C%22%22)%3C%2FP%3E%3CP%3E2.%20Select%20the%20whole%20section%20and%20conditional%20formatting%20-%20highlight%20cells%20based%20on%20their%20values%20-%20format%20both%20Font%20and%20Fill%20to%20same%20color%20(look%20into%20Custom%20Color%20HEX%23%20to%20pick%20the%20same%20color%20grade)%20if%20value%20%3D%201%2C%20and%20%3D%20White%20if%20value%20%3D%200%3C%2FP%3E%3CP%3EI%20also%20changed%20the%20H5%20to%20match%20Project_Start%20named%20range%20instead%20of%20hardcoding%20the%20date.%20Note%20that%20Project_Start%20should%20refer%20to%20D3%20instead%20of%20D3%3AE3.%3C%2FP%3E%3CP%3EI%20strongly%20recommend%20against%20using%20Merged%20cells%20anywhere%20in%20your%20worksheet.%20Instead%2C%20select%20the%20whole%20section%20you%20want%20to%20have%20the%20text%20centered%2C%20right%20click%20to%20Format%20Cell%2FAlignment%2FCenter%20across%20Selection.%20It%20will%20help%20to%20center%20your%20text%20as%20if%20all%20the%20cells%20were%20merged%20but%20no%20cell%20is%20actually%20merged%2C%20enabling%20you%20to%20refer%20to%20the%20specific%20cell%20in%20another%20formula%2Ffunction.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

HI ,

i need assistant on the issue on my gantt chart bar unable to change when i change the start and end date as per attachment. 

1 Reply
Highlighted

@sootheng I am not quite sure how you currently generate your Gantt chart? Did you do it by manually color the cells?

Please see the attached sample workbook. I only present the suggested solution to Developmental section (row 8 to 15) and Implementation section (row 20 to 26). My solution is very basic:

1. Add a formula in each cell from column H to the right-most column: =IFERROR(IF(AND(H$5>=$D8,H$5<=$E8),1,0),"")

2. Select the whole section and conditional formatting - highlight cells based on their values - format both Font and Fill to same color (look into Custom Color HEX# to pick the same color grade) if value = 1, and = White if value = 0

I also changed the H5 to match Project_Start named range instead of hardcoding the date. Note that Project_Start should refer to D3 instead of D3:E3.

I strongly recommend against using Merged cells anywhere in your worksheet. Instead, select the whole section you want to have the text centered, right click to Format Cell/Alignment/Center across Selection. It will help to center your text as if all the cells were merged but no cell is actually merged, enabling you to refer to the specific cell in another formula/function.