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

I have done this before, but I am a little sketchy on remembering how to do it.

Baiscally, if I remember correctly, what you need to is to add a series for each of them, Network Servicing Series, Network Box Upgrade series etc. in the chart.

Then you need to have a summary table for each of your headers which uses an IF statement to only select the relevant data, so the rest of the series is 0.

When you plot this, there will be a colour for each series by default, which you can then change.

So if you have 4 categories, you want a summary table with 4 rows, then the chart is plot off the summary table, giving 4 columns of differing colours.

I am a bit busy today, if I get chance i'll show you.

Also, all of this doesn't really need to be done by code. Using Specific Text - Contains in the conditional formatting should do the first bit, and a chart off a summary table wont need code either, just setting up in line with the conditional formatting.