Gantt Bar Conditional Formatting not performing as expected

michael anderson

Hello Community,

I am attempting to use conditional formatting to change the color of my Gantt bars; however, the conditional format is not being applied to the Gantt bars.

Here is my conditional formatting

You can see below that I specify a change in row color and Gantt bar color based upon values in booSummaryRow.

The problem is that the conditional formatting is being applied to the row color, not the Gantt bar.

The values in booSummaryRow are populated by the following formula:

=IF(COUNT(ANCESTORS(Primary97)) = 0,"X", COUNT(CHILDREN(Primary97)))

If the count of ancestors for the current row = 0 set booSummaryRow to “X”

If the count of ancestors for the current row is > 0 set booSummaryRow to the number (count) of children associated to that row.

As you can see from the above picture,

1. When booSummaryRow contains an “X” the Gantt bar color is light gray, not green.
2. When the value in booSummaryRow is greater than a 0 the bar light gray, not dark gray
3. The conditional formatting does perform as expected when booSummaryRow is equal to 0.

Now here is where it gets weird. If I outdent a group of rows equal to or greater 0 all the way out, so that booSummaryRow contains an “X” the Gantt bars change color as expected.

Trouble shooting steps I tried without success:

Cleared all formatting

1.      First I disabled all of my conditional formatting rules.

2.      Next I used the Clear Formatting command on the entire sheet

3.      Then I re-enabled the conditional formatting

Disabled all other conditional formatting

1.      First I disabled all other conditional formatting on the sheet.

2.      Then I disabled, one at a time, each of the three conditional formats shows above

Changed the order in which the conditional formatting is applied

1.      First I moved “if booSummaryRow is ‘X’” to the third position; moving “if booSummaryRow is 0” to the first position; and moving “if booSummaryRow is greater than 0” to the second position

2.      Next I moved “if booSummaryRow is 0” to the first position; moving “if booSummaryRow is ‘X’” to the second position; and moving "if booSummaryRow is greater than 0 to the third position.

None of these steps helped.

Any assistance would be greatly appreciated.

Thank you.

Michael Anderson

Genevieve P.

Hi Michael,

Wow, thank you for all of your troubleshooting steps and screen captures! This is great information.

Parent rows become a bit more complex when they are linked with Project Settings and Gantt Charts. Parent tasks automatically take on the earliest start date of their children, and the latest end date their children. This means that their dates aren't editable, and the bar in the Gantt chart can't be adjusted.

Currently it's expected behaviour that you can't customize the colour of a Gantt bar for a Parent Row with child tasks, it will always show up as a light grey. You can test this by trying to manually change the bar by right-clicking on it - no option to change colour will appear, as it does with child tasks. (Please submit an Enhancement Request to Product team about this!)

That's why when you move all the tasks out so that they don't have any children tasks anymore, the bars change colour as expected; those tasks are no longer parent tasks and their dates are customizable again. You can see that the Conditional Formatting rules are working properly because the background of the cells in the grid view are changing as expected, even when the Gantt bar does not. (Click here for more information on Parent Rollup functionality).

Let me know if you have any more questions about this!

Cheers,

Genevieve