Conditionally format scatter plot dot based on drop down loast value in cell

Dear all,

I'm updating my manager's project milestone plan to make it more dynamic withiut changing his look. See attached. Top is the old and bottom one is my new one.

I am trying to conditionally format the colour of the currently static purple triangles (series task#) in my chart (modified scatter plot) based on the selection in the drop down in column S. For example if the End date is overdue you can change the colour of the triangle in the chart by selecting red from the drop down in column S.

On Track the triangle is purple, Amber in orange, Red in red and so one. I can't seem to be able to do this.

Any suggestions? And is it possible without VBA?

Thanks so much.

Rory