VBA to Alter All Charts on Page, All Points Per Chart

[Marmit424]

Hi,

My goal is to instantly standardize the colors of any chart on an active worksheet. In completing this, I'm attempting to get my code to automatically determine how many sections there are per chart to fill in and then to fill that number in with pre-selected colors.

My issue is I'm struggling to understand how to loop this properly, specifically for the RGB input values. Each time I go to the next section to fill, I need the R#+1, G#+1, B#+1. However, in pairing a text "R" with a number like 1 (as I've done below) the variable becomes a text string and no longer refer's back to the number I assigned. That is, instead of returning R1 --> 55 it returns "R1" into the RGB value which does nothing.

I researched further and tried arrays but became confused with how to pass those through rgb values. I can post that code as well though.

Thanks!

'First Color
R1 = "55"
G1 = "70"
B1 = "73"


'Second Color
R2 = "1"
G2 = "184"
B2 = "170"


'Third Color
R3 = "0"
G3 = "176"
B3 = "80"


ChartType = InputBox("Are you coloring Donut Graphs?")
If ChartType = "Yes" Then
'Setting Donut Chart Colors
Dim cht As Chart
Dim x As Long
For i = 1 To ActiveSheet.ChartObjects.Count
Set cht = ActiveSheet.ChartObjects(i).Chart
For Y = 1 To cht.SeriesCollection(1).Points.Count
R = "R" & Y
G = "G" & Y
B = "B" & Y
cht.SeriesCollection(1).Points(Y).Interior.Color = RGB(R, G, B)
cht.SeriesCollection(1).Format.Line.ForeColor.RGB = RGB(255, 255, 255)
Next Y
Next i

Else:
MsgBox ("This can only be used for donut graphs.")
End If


End Sub

 

[Domenic]

If you declare and define a variable in this manner....

[FONT=Arial]    [COLOR=darkblue]Dim[/COLOR] vColors [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR]
    
    vColors = Array(Array(55, 70, 73), Array(1, 184, 170), Array(0, 176, 80))[/FONT]

...you can use the following within your For/Next loop...

[FONT=Arial]
        R = vColors(Y - 1)(0)
        G = vColors(Y - 1)(1)
        B = vColors(Y - 1)(2)[/FONT]

Alternatively, you can use the Switch function. For example, within your For/Next loop, you can define R as follows...

[FONT=Arial]    R = Switch("R" & Y = "R1", 55, "R" & Y = "R2", 1, "R" & Y = "R3", 0)[/FONT]

And you can define G and B the same way.

Hope this helps!

 

[Marmit424]

Thank you!!! This worked perfectly.