Hello all,
I am attempting to build a form of a Roadmap view of a number of projects to be completed in my team.
I hope this works:
https://docs.google.com/leaf?id=0B_hcSzRskiH_NjFkZGJkNjEtN2QxYi00ZjdjLTk4YWMtZGRiY2ZlZDAyYzJl&hl=en
Column A: Project Name + Area Project is affecting + Tech effected (these have been made up for this example)
Column B: Project Name
Column C: Start Date
Column D: End Date
Column E: Area
Column F: Tech
Column G: Duration
Column H: RAG Status
I have used Column A for the labels on the graph, and have made the graph through the traditional approach (I believe) of filling in the "Start Date" part.
I've fixed the X-axis via VBA to the cells M1 and M2, to give me todays date and then the end date of the longest project. This is controlled via the current "rejig graph" button.
What I would now like to do is to change the colour of the projects on the graph based on the information in Column H. I do not know how to do this so any help would be appreciated.....
I have had thoughts around using arguments such as IF Red then use X on the colour palette, or whether it would be better to do a lookup of colour against palette within the excel sheet first, and then ask the vba to look at the number as the guide.
The next bit then will be for me to make the references to the durations + start dates to be dynamic as this sheet will be expanded over time and I don't want to have to keep amending the graph. I have worked with dynamic ranges before and aware their are lots of help topics on here for that).
Thank you in hopeful advance
djp203
I am attempting to build a form of a Roadmap view of a number of projects to be completed in my team.
I hope this works:
https://docs.google.com/leaf?id=0B_hcSzRskiH_NjFkZGJkNjEtN2QxYi00ZjdjLTk4YWMtZGRiY2ZlZDAyYzJl&hl=en
Column A: Project Name + Area Project is affecting + Tech effected (these have been made up for this example)
Column B: Project Name
Column C: Start Date
Column D: End Date
Column E: Area
Column F: Tech
Column G: Duration
Column H: RAG Status
I have used Column A for the labels on the graph, and have made the graph through the traditional approach (I believe) of filling in the "Start Date" part.
I've fixed the X-axis via VBA to the cells M1 and M2, to give me todays date and then the end date of the longest project. This is controlled via the current "rejig graph" button.
What I would now like to do is to change the colour of the projects on the graph based on the information in Column H. I do not know how to do this so any help would be appreciated.....
I have had thoughts around using arguments such as IF Red then use X on the colour palette, or whether it would be better to do a lookup of colour against palette within the excel sheet first, and then ask the vba to look at the number as the guide.
The next bit then will be for me to make the references to the durations + start dates to be dynamic as this sheet will be expanded over time and I don't want to have to keep amending the graph. I have worked with dynamic ranges before and aware their are lots of help topics on here for that).
Thank you in hopeful advance
djp203
