Excel VBA Time Series Graph

Hi,

Very new to vba (well been 5 years) so please advise if there is anything missing or confusing.

I am trying to create a time series graph (bar graph) which illustrates a timeline of when projects will be started. I am able to create this graph by the engineer placing the year and quarter into a column which will then be graphed accordingly. The next phase of this spreadsheet is for the engineer to place a status into a column which will automatically change then data point colour to a predefined colour.

For example, table.

Column A | Column B | Column C | Column D | Column E | Column F | Column G |
Row 1: Task Name | Year | Quarter | Start Date| End Date | Length | Status |
Row 2: Server 2012| 2013 | 3 | 1/07/2013 | 30/12/2013 | 91 | Review (fill colour blue) |
Row 3: lync 2012| 2013 | 3 | 1/07/2013 | 30/12/2013 | 91 | complete (fill colour green)|
Row 4: exchange 2012| 2013 | 3 | 1/07/2013 | 30/12/2013 | 91 | review (fill colour blue) |

The approach i have taken to date is to code a vba macro which looks for predefined text in a column and if matches then set cell fill colour, which works. I then want to graph this data point to be set to the same fill colour. To date all i am able to do is change the fill colour of the cell which does not cross over to the graph.

For example the graph and each data point would be coloured as such
Review: blue
approved: purple
Rejected: Red
Complete: Green

I have attached my xsl file to help.

Thanks,
Daniel