Need help with VBA loop function

[Akbarov]

Hello dear community,
I need help with loop function in following VBA

Sub ColorOfChart()

    Dim chrt As Chart, i As Long, clr As Long
    Dim r As Byte, g As Byte, b As Byte
 
    Set chrt = Sheet31.ChartObjects(1).Chart
                
    For i = 1 To chrt.SeriesCollection.Count
        clr = Sheet31.Cells(68, i).Interior.Color
        r = clr Mod 256
        g = clr \ 256 Mod 256
        b = clr \ 65536 Mod 256
        chrt.SeriesCollection(i).Format.Fill.ForeColor.RGB = RGB(r, g, b)
     Next i
 
End Sub

How can I make this part "Sheet31.ChartObjects(1).Chart"
to be
Sheet31.ChartObjects(2).Chart
Sheet31.ChartObjects(3).Chart
Sheet31.ChartObjects(4).Chart
etc.. There are around 50 chart objects.

Can anyone help me please?

 

[Colo]

Hi,
You can look all the ChartObjects using For Each Next method as follows.

Sub ColorOfChart()
    Dim chrtObj As Object
    Dim chrt As Chart, i As Long, clr As Long
    Dim r As Byte, g As Byte, b As Byte

    For Each chrtObj In Sheet31.ChartObjects
        Set chrt = chrtObj.Chart
        For i = 1 To chrt.SeriesCollection.Count
            clr = Sheet31.Cells(68, i).Interior.Color
            r = clr Mod 256
            g = clr \ 256 Mod 256
            b = clr \ 65536 Mod 256
            chrt.SeriesCollection(i).Format.Fill.ForeColor.RGB = RGB(r, g, b)
        Next i
    Next
End Sub

 

[Akbarov]

Hello, thank you for reply. I used your code. But all data labels disappeared after that.. I don't know what I did wrong
I have Data series. Let's say from column 6 to 16 , 16 to 26 , 26 to 36 and so on..
And below each 10 column there is 1 chart. My code works fine for 1st chart. I want it to do same thing for all charts

 

[Akbarov]

First Chart still works fine, bu next chart all data bars are white

 

[Colo]

Okay, back to basic. I just added looping for 50charts.
Just change the maximum looping count for a variable named "x", from 50 to your needs.

Sub ColorOfChart()
    Dim chrt As Chart, i As Long, clr As Long
    Dim r As Byte, g As Byte, b As Byte
    Dim x As Long

    For x = 1 To 50    'Change here to suit your number of charts
        Set chrt = Sheet31.ChartObjects(x).Chart

        For i = 1 To chrt.SeriesCollection.Count
            clr = Sheet31.Cells(68, i).Interior.Color
            r = clr Mod 256
            g = clr \ 256 Mod 256
            b = clr \ 65536 Mod 256
            chrt.SeriesCollection(i).Format.Fill.ForeColor.RGB = RGB(r, g, b)
        Next i
    Next x
End Sub

 

[Jaafar Tribak]

I see this is related to your recent question here:

VBA Chart series color based on cell color

Hello Dear community, I want to know is there a way to make my chart series color dynamic? So let's say A2:T2 are cells with different colors. I want My Series 1 to change it is color based on cell A2 color My Series 2 to change it is color based on cell B2 color and so on I tried this code...

www.mrexcel.com

You are not saying which are the data ranges for each chart and their respective colored cells.

 

[Akbarov]

I tried , but still same. First Chart changes color, others just disappear ( or become white )

 

[Colo]

All right, Would you try to change the following part in your original code manually then if the code works for the second chart as intended?
If it doesn't work, even if we make it as a loop, it won't work.

Set chrt = Sheet31.ChartObjects(2).Chart

 

[Akbarov]

Can this image be helpfull? Colors on row 68 should be in chart bars.

 

[Jaafar Tribak]

The iimage shot is not very clear but If you have 10 series in each chart and the colored cells start in cell F68 then you may try the following :

Sub ColorOfChart()
    Dim chrtObj As Object
    Dim chrt As Chart, i As Long, j As Long, clr As Long
    Dim r As Byte, g As Byte, b As Byte

    For Each chrtObj In Sheet31.ChartObjects
        Set chrt = chrtObj.Chart
        For i = 1 To chrt.SeriesCollection.Count
            clr = Sheet31.Cells(68, i + 5 + j).Interior.Color
            r = clr Mod 256
            g = clr \ 256 Mod 256
            b = clr \ 65536 Mod 256
            chrt.SeriesCollection(i).Format.Fill.ForeColor.RGB = RGB(r, g, b)
        Next i
        j = j + 10
    Next
End Sub