Changing the color of the bars in a Gantt chart based on a value

Welcome to the forum!

I think this can be achieved with some helper columns. Created three new columns (one for each time category) and use a formula to show the 'days on air' only in the column for the appropriate number of days. In the attachment, I created the helper columns in G:I and used the following formula in G3:

=IF(AND($F3>=G$2,OR($F3<=H$2,H$2="")),$F3,"")

Fill right through I3 and down and you should see the days on air in the appropriate column and blanks everywhere else. For presentation purposes, you can hide these columns if you need a better looking data table, but these new columns are the ones that you will show in your chart using a stacked bar chart. Each 'Spot Name' will get four stacked bars, but you can "white out" the date bar the way you did in your attachment, then two of the numeric bars will be zero (blank), while the other will show the days on air. The most important difference from your current chart is that the visible numeric bars are pulled from three separate columns, so they can be three different colors (red, yellow, green).

I hope that's not too confusing? Take a look at the attachment to see if that helps:

Glad to help! Feel free to reply if you're having issues or it's not working properly.

Good luck!