Excel VBA, Bar Chart. Change bar color based on cell value

I have a bar chart in Excel.  The chart is displaying data from Column C and D.  Column C is a job description and column D is estimated hours to complete the job.  In Column E i have some text with Categories.  Each category has its own color.  I would like the bars in my chart to be color coded based on the category they are in.  Is this possible to do using some VBA code in Excel?
jnikodymAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rory ArchibaldCommented:
Is it possible to rearrange the data at all? It might make this easy to do manually.

Also, can you post an example workbook?
jnikodymAuthor Commented:
I don't want to do it manually.  I have a button on the sheet that has vba code behind it that automatically creates the excel gantt chart.  It all works great, but i would just like to be able to categorize the bar lines with colors.
psteffCommented:
Without a sample workbook, it is harder to give a solution in this case - but in your scenario, if you are NOT using a regular built-in Excel chart, you might be able to use conditional formatting on your gantt chart (depending on how you produced your gantt chart).
jnikodymAuthor Commented:
I am using a stacked bar chart which you can manipulate a little to make it a gantt chart.  I just need the correct vba code to step through the bars to look for a certain value in my data set and color the bars based on that value.
psteffCommented:
Depending on the data, another possible way of doing this is to add more columns (or rows) for your data so that each category of data has it's own column (or row) in your table.  Via formula, you could easily have the data put into the various columns (or rows) so that you have each category of data in it's own column (or row) so that it can have its own color.  As you stack the chart, the columns (or rows) with blank data will have no effect, and the columns (or rows) with the data will have its respective color.  Therefore, you would not even need to code it into VBA.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.