Forum Discussion

TxSRV6664's avatar
TxSRV6664
Copper Contributor
Nov 08, 2023

Conditional formatting calendar in a Gantt Chart

I am using a Gantt chart to schedule crews and I would like to have it color coordinated to easily see if anyone is missing from the schedule at a glance. 

 

What I want, is that if I pick a name from the drop down list in column C, for it to automatically change the color in the calendar portion to match what I have set. e.g. Bill = green, Bob = Blue.  My calendar starts on J, and shows through BN. Names would start on C9 and stay on the C column. 

 

 

excel
  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Nov 08, 2023

    TxSRV6664 

    =($C9="ann")*($H9<=J$8)*($I9>=J$8)
    =($C9="bob")*($H9<=J$8)*($I9>=J$8)

    Do these rules for conditional formatting return the intended result in the screenshot?

    =$J$9:$BN$12

    This is the range the conditional format applies to.

    • TxSRV6664's avatar
      TxSRV6664
      Copper Contributor
      Nov 09, 2023

      OliverScheurich 

      I made some changes and got rid of a column.  But I think some of the formulas that are built into the template are interfering with the formula you suggested. 

      I tried to modify what you sent to match what is going on with my modified spreadsheet.  Not having luck though.   I feel like I should be able to edit one of the ones that starts with =AND(NOT(ISBLANK  to get the results.  But I need to learn the formula stuff better. 

       

       

       

Resources