How to change the filled color if gantt chart displays completed

Question

Hi everyone. I want to change filled color automatically based on original conditional formatting if gantt chart displayed "completed". I tried new conditional formatting =isnumber(search("completed", C5), however, it seemed not working out. What could I do?&nbsp;

hansvogelaar · Answer

cchildaa&nbsp;
Try
&nbsp;
=isnumber(search("completed", $C5))

cchildaa · Answer

HansVogelaar&nbsp;I tried, but it didnt work out

hansvogelaar · Answer

cchildaa&nbsp;
Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar? Thanks in advance.

cchildaa · Answer

https://1drv.ms/f/s!AkiH05FJrWZykS4Nxw8a7fnzjGIf?e=tL7eWIPlease find the file from attached link for your reference.Thanks for your help.

hansvogelaar · Answer

cchildaa&nbsp;
Thanks. Your rule applies to I5:Z5 and its formula is
&nbsp;
=AND(ISNUMBER(SEARCH("completed",C5)),AND(ISBLANK(D5)=FALSE, ISBLANK(E5)=FALSE, ISBLANK(F5)=FALSE, ISBLANK(G4)=FALSE, ISBLANK(H5)=FALSE, ISBLANK(I5)=FALSE, ISBLANK(J5)=FALSE, ISBLANK(K5)=FALSE, ISBLANK(L5)=FALSE, ISBLANK(M5)=FALSE, ISBLANK(N5)=FALSE, ISBLANK(O5)=FALSE, ISBLANK(P5)=FALSE, ISBLANK(Q5)=FALSE, ISBLANK(R5)=FALSE, ISBLANK(S5)=FALSE, ISBLANK(T5)=FALSE, ISBLANK(U5)=FALSE))
&nbsp;
In the first place, for this to work, ALL column references should be absolute: $C5, $D5, $E5 etc.
In the second place, it requires that ALL cells in C5:U5 are filled (none of them is blank). Is that really what you want?

Forum Discussion

How to change the filled color if gantt chart displays completed

Share

Resources