Need help with VBA loop function

Akbarov

Active Member
Joined
Jun 30, 2018
Messages
347
Office Version
  1. 365
Platform
  1. Windows
Hello dear community,
I need help with loop function in following VBA

VBA Code:
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?
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Hi,
You can look all the ChartObjects using For Each Next method as follows.

VBA Code:
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
 
Upvote 0
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
 
Upvote 0
First Chart still works fine, bu next chart all data bars are white
 
Upvote 0
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.

VBA Code:
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
 
Upvote 0
I see this is related to your recent question here:

You are not saying which are the data ranges for each chart and their respective colored cells.
 
Upvote 0
I tried , but still same. First Chart changes color, others just disappear ( or become white )
 
Upvote 0
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.

VBA Code:
Set chrt = Sheet31.ChartObjects(2).Chart
 
Upvote 0
Can this image be helpfull? Colors on row 68 should be in chart bars.
2022-07-19.png
 
Upvote 0
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 :
VBA Code:
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
 
Upvote 0

Forum statistics

Threads
1,212,938
Messages
6,110,771
Members
448,297
Latest member
cocolasticot50

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top