By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,336 Members | 2,307 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,336 IT Pros & Developers. It's quick & easy.

Changing Individual Column Colours in a barchart

P: 2

I need to be able to change individual column's colour in a series in bar charts, i.e.

IF Column Value > Target THEN
Column Colour = Green
ELSEIF Column Value = Target THEN
Column Colour = Orange
ELSE Column Colour = Red

I need to be able to do it for all the charts in the Workbook and for each column in each chart.

But I don't know how to do this in VB or even if it can be done.

Does anyone have any suggestions?

Regards, Charls
Apr 19 '07 #1
Share this Question
Share on Google+
1 Reply

Expert 100+
P: 807
Assuming that you only have Chart sheets, this code changes anything with a value > 12 to green. If you have charts that are embedded on Worksheets, then you must first iterate through the Worksheets collection, then for each worksheet, iterate through the ChartObjects collection. I left the elseif's and color to you. To figure out the rest, record a macro and set a column manually. Let me know if you have embedded charts. HTH --Sam
Expand|Select|Wrap|Line Numbers
  1. Sub Macro2()
  2.     Dim c As Chart
  3.     For Each c In ActiveWorkbook.Charts
  4.         Dim s As Series
  5.         Set s = c.SeriesCollection(1)
  6.         For i = 1 To s.Points.Count
  7.             If s.Values(i) > 12 Then s.Points(i).Interior.ColorIndex = 10
  8.         Next i
  9.     Next c
  10. End Sub
Apr 19 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.