Forum Discussion

Copper Contributor

Jun 02, 2024

How to change the filled color if gantt chart displays completed

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"...

excel

HansVogelaar

MVP

Jun 02, 2024

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?

cchildaa

Copper Contributor

Jun 04, 2024

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

HansVogelaar
MVP
Jun 04, 2024
cchildaa

Do you really want to require that ALL cells in C5:U5 are filled (none of them is blank)?
- cchildaa
  Copper Contributor
  Jun 05, 2024
  HansVogelaar 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
  - HansVogelaar
    MVP
    Jun 05, 2024
    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.
    gc.xlsx25 KB