Dynamic Chart Coloring

bswain2000 · Jul 19th 2004

I want to build bar charts (and possible other types) where the colors of the bars are a direct function of the value being charted. For example, I'd like to build an index chart where values above 100% result in a green bar, whereas values below 100% result in a red bar. I'm useless in VBA, so any assistance would be greatly appreciated.

Cheers,

Brian

Jong · Jul 19th 2004

Each bar on a chart corresponds to a series.

To do what you are trying to achieve, you may be able to incorporate some of the below code:

Code

Dim aVals as variant


'First select the chart you are working with
Worksheets("SHEET NAME").ChartObjects(1).Activate


'Loops through each series, checking the values
for i = 1 to ActiveChart.SeriesCollection.Count
    
      With ActiveChart.SeriesCollection(i)
           aVals = .Values


           if aVals(1) > 100 then
                .interior.colorindex = 3
           else
                .interior.colorindex = 4
           end if


      end With
next i

Display More

Please note, this code is untested, just trying to point you into the right direction

Dynamic Chart Coloring

Participate now!

Share