How to use VBA to colour pie chart

Question

I have the following code in which the code tries to create a bubble chart with pie charts as the bubbles. As in this version colour themes are used to create a different colour in each pie chart (bubble) in the function part I have the problem that it works depending on the paths to the colour palettes.

Is there an easy way to make the function in a way that it works independently of those paths either by coding a colour for each pie chart segment or by using standardize paths (probably not possible, not preferable).

    Sub PieMarkers()

Dim chtMarker As Chart
Dim chtMain As Chart
Dim intPoint As Integer
Dim rngRow As Range
Dim lngPointIndex As Long
Dim thmColor As Long
Dim myTheme As String


Application.ScreenUpdating = False
Set chtMarker = ActiveSheet.ChartObjects("chtMarker").Chart
Set chtMain = ActiveSheet.ChartObjects("chtMain").Chart

Set chtMain = ActiveSheet.ChartObjects("chtMain").Chart
Set rngRow = Range(ThisWorkbook.Names("PieChartValues").RefersTo)

For Each rngRow In Range("PieChartValues").Rows
    chtMarker.SeriesCollection(1).Values = rngRow
    ThisWorkbook.Theme.ThemeColorScheme.Load GetColorScheme(thmColor)
    chtMarker.Parent.CopyPicture xlScreen, xlPicture
    lngPointIndex = lngPointIndex + 1
    chtMain.SeriesCollection(1).Points(lngPointIndex).Paste
    thmColor = thmColor + 1
Next

lngPointIndex = 0

Application.ScreenUpdating = True
End Sub

Function GetColorScheme(i As Long) As String
Const thmColor1 As String = "C:\Program Files\Microsoft Office\Document Themes 14\Theme Colors\Blue Green.xml"
Const thmColor2 As String = "C:\Program Files\Microsoft Office\Document Themes 14\Theme Colors\Orange Red.xml"
    Select Case i Mod 2
        Case 0
            GetColorScheme = thmColor1
        Case 1
            GetColorScheme = thmColor2
    End Select
End Function

The code copies a single chart again and again on the bubbles. So I would like to alter the Function (now called Get colourscheme) into a function that assigns a a unique RGB colour to each segment of each pie chart. A similar issue is discussed here Change the Point Color in chart excel VBA but the code apparently did not work for the person who was asking. Could anybody give me any advice on how to rewrite the function part of the code

My rough approach would be:

select the worksheet and then grab each graph after it is copied
change the colour of each segment with a unique RGB code

But how I would implement it into VBA is not clear to me. I would really appreciate ANY comments on the issue.

Tim Williams Tim Williams · Accepted Answer · 2013-06-29T23:56:24

Here's how to set the colors of each slice in a pie chart. Not sure how you want to determine which slice gets what color.

Dim clr As Long, x As Long

For x = 1 To 30
    clr = RGB(0, x * 8, 0)
    With ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1).Points(x)
        .Format.Fill.ForeColor.RGB = clr
    End With
Next x

How to use VBA to colour pie chart

1 Answers