How to change the filled color if gantt chart displays completed

[cchildaa]

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? 

[#2]

@cchildaa 

Try

=isnumber(search("completed", $C5))

[#3]

@Hans Vogelaar I tried, but it didnt work out

[#4]

@cchildaa 

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.

[#5]

https://1drv.ms/f/s!AkiH05FJrWZykS4Nxw8a7fnzjGIf?e=tL7eWI

Please find the file from attached link for your reference.

Thanks for your help.

[#6]

@cchildaa 

Thanks. Your rule applies to I5:Z5 and its formula is

=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))

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?

[#7]

thanks for your reply. It doesn’t work. I dont know why. :loudly_crying_face::loudly_crying_face::loudly_crying_face:

[#8]

@cchildaa 

Do you really want to require that ALL cells in C5:U5 are filled (none of them is blank)?

[#9]

@Hans Vogelaar I just wanna change the cell color if C5 displayed “completed”. Like pic 1 changed to pic 2

pic 1

pic 2

 

However, I don’t know why the above formula not working

[#10]

@cchildaa 

Change the formula to

=ISNUMBER(SEARCH("completed",$C5))

and apply it to =$I$5:$Z$34

You may want to inspect the rest of the conditional formatting rules.