Dynamic charting by VBA

Hi Guys,

In our regular reporting schedule we have an excel dependent scenario where we think we might need some help to come up with a solution. We need to prepare a report on Gross Margin Analysis so we prepare a line chart showing price & cost movements for different time periods.I have attached a file which will help you to understand what I mean.The Cost line is in red colour and price line is in green.

The problem: It is obvious that some time the price/cost will go higher/lower. Till now everything is fine. We can easily draw the line chart. But the challenge comes here as we have been asked to colour the graph showing the movement of Price vs cost. That means if the cost is higher it should be coloured as red where the colouring to be done as green if price is higher. You can check the graph to understand what I mean.Now it is consuming hell lot of time for us as there are around 150 graphs to be prepared and coloured accordingly. Currently we go to Insert-Shapes-Free form tool then we draw it according to the movement and then colour.

We think there might be a solution for this using VBA or some other way round. Hope I have been able to convey the scenario properly to you. In case of any query you can revert.