Coloring chart based on range values

[mikelowski]

I want to color a chart depending on range values. From 0 to 29.949 one color, from 29.950 to 39.950 another one, and so on. Here's the code I've come up with, but it doesn't work so far, it just colors all columns with the last Else argument.

  Sub ColorChartMacro()
    Dim i As Long
    For i = 1 To ActiveChart.SeriesCollection.Count
        With ActiveChart.SeriesCollection(i)
            If WorksheetFunction.Sum(.Values) < 29.949 Then
                .Interior.Color = RGB(217, 0, 0)
            ElseIf WorksheetFunction.Sum(.Values) < 39.949 Then
                .Interior.Color = RGB(255, 102, 0)
            ElseIf WorksheetFunction.Sum(.Values) < 59.949 Then
                .Interior.Color = RGB(255, 204, 0)
            ElseIf WorksheetFunction.Sum(.Values) < 69.949 Then
                .Interior.Color = RGB(153, 204, 0)
            Else
                .Interior.Color = RGB(0, 128, 0)
            End If
        End With
    Next
End Sub

 

[Somendra Misra]

Hi,

Do you want VBA solution only, because what you want can be done through formulas itself, by adding dummy series.

Regards,

 

[mikelowski]

Hi,

Yes, I know I can do it just with the IF function but I'd like to do it through VBA.

Thanks.

 

[NARAYANK991]

Hi ,

How many series does your chart have , and how many columns does each series have ?

You are using SeriesCollection(i) to look at a series , but within a series , you are not looking at individual data points since you are using .Values

Can you step through the code and see what the following contains ?

WorksheetFunction.Sum(.Values)

Narayan

 

[mikelowski]

Actually, I don't have any series. It's a column chart.

 

[NARAYANK991]

Hi ,

Every chart has a series ; anyway your chart has just one series.

So , going by the fact that you are using .Values , all your columns will be coloured one colour , based on what the following returns :

WorksheetFunction.Sum(.Values)

Is this what you want ? or do you want that each column should be coloured differently based on its value ?

If you can upload your workbook , it will be a matter of minutes to resolve the issue.

Narayan

 

[mikelowski]

Hi,

Indeed! Every column should be colored depending on the different values. Here you have the file, thanks!

 

[NARAYANK991]

Hi ,

See your file.

Narayan

 

[mikelowski]

Great! Thank you very much.