CODEWITHSUNDEEP
vbams-access
Fil
Fil

Reputation: 471

Looping though Series collection in MS Chart

I have a chart which I would like to change the color of its columns if the label value on the SeriesCollections DataLabels is greater than the Value on its sister DataLabel For example if SeriesCollection(1) with DataLabel(1) > DataLabel(2) Then .SeriesCollection(1).Points(1).Interior.Color = RGB(250, 0, 0)

I have this code which misses out some SerieCollections and also changes the color of the columns regardless of the DataLabel.Caption Value

Dim labelCaption As Currency
Dim k As Integer
Dim j As Integer
Dim c As Object
Set c = myChart.Object

With c
 For k = 1 To .SeriesCollection.Count
   For j = 1 To .SeriesCollection(k).Points.Count
   labelCaption = .SeriesCollection(k).Points(j).DataLabel.Text
   If labelCaption > .SeriesCollection(k).Points(j).DataLabel.Text Then
   .SeriesCollection(k).Points(j).Interior.Color = RGB(250, 0, 0)
   Else
   .SeriesCollection(k).Points(j).Interior.Color = 65280
   End If
   Next j
    
 Next k
End With

Edit The RowSource of myChart is:

Me.myChart.RowSource = "SELECT [AreaDonor],[NetDonation],[DonationLimit] FROM [qryDonationComparison];"

In other words if the value of NetDonation is greater than the value of DonationLimit then change the color of colunm corresponding to that DataLabel with NetDonation to red else leave it green.

This is the sample data.

enter image description here

This is the Chart I am getting:

enter image description here

Upvotes: 1

Views: 137

Answers (1)

June7
June7

Reputation: 21370

This works for me:

With c
For j = 1 To .SeriesCollection(1).Points.Count
    If CDbl(.SeriesCollection(1).Points(j).DataLabel.Text) > CDbl(.SeriesCollection(2).Points(j).DataLabel.Text) Then
        .SeriesCollection(1).Points(j).Interior.Color = RGB(250, 0, 0)
    Else
        .SeriesCollection(1).Points(j).Interior.Color = 65280
    End If
Next j
End With

Upvotes: 1

Related Questions