Custom bar color based on value should stay when autofiltering

[carowen]

Hi
For our project list I have created a GANTT chart in Excel. On the left side is a XL table with all necessary information, on the right next to it is the Chart. Through VBA I recolor single data points based on a criteria in the table. This works fine on the whole table.
However, when I use the autofilter in the table, the colors in the chart are not correct anymore. I know this is because the position changed, the chart just 'knows' point 1=red, point 2=blue, point 3=red,.... When I apply the filter the value which was initially at position 5 and in the chart was blue is maybe now at position 1 and in red because of the initial coloring, while it still should show in blue.

Is there a way to 'hardcode' the bar color, so it does not change when applying the filter?

An example of the file and code used to recolor is attached.

Thanks for your help.

[Andy Pope]

Rather than code why not use additional stacked bar series and formula to automatically display the correct bar colour.
Add 3 columns to your table with formula to determine whether to plot a Day value or NA.

RedBar in column Y: =IF(T3="Yes",[@Days],NA())
GreyBar in column Z: =IF(T3="Yes",NA(),IF([@Status]="H",[@Days],NA()))
Bluebar in column AA: =IF(T3="Yes",NA(),IF([@Status]="N",[@Days],NA()))

Change the data reference for DAY series to RedBar and add 2 more series for Grey and Blue.
Change the chart type for the 2 new series as they will default to same as last series in chart.
Adjust data range references and change plot order so ReportDay is still last series.

Format the 3 series to match descriptive name.

[carowen]

Andy
Thank you very much for this. It works! Great help.
Thanks, Caroline