Excel VBA to fill pie chart colors from cells with conditional formatting

Question

I don't have experience with VBA and I'm trying to format all of the pie charts on one active sheet based on the colors of their data cells in Excel 2010. I found this code from: http://datapigtechnologies.com/blog/index.php/color-pie-chart-slices-to-match-their-source-cells/

Sub ColorPies()
Dim cht As ChartObject
Dim i As Integer
Dim vntValues As Variant
Dim s As String
Dim myseries As Series

    For Each cht In ActiveSheet.ChartObjects
        For Each myseries In cht.Chart.SeriesCollection

            If myseries.ChartType <> xlPie Then GoTo SkipNotPie
            s = Split(myseries.Formula, ",")(2)
            vntValues = myseries.Values

            For i = 1 To UBound(vntValues)
                myseries.Points(i).Interior.Color = Range(s).Cells(i).Interior.Color
            Next i
SkipNotPie:
    Next myseries
Next cht
End Sub

This code works well, however it is unable to pick up the colors from conditional formatting.

I came across this solution for VBA to read conditional formatting colors:

Selection.FormatConditions(1).BarColor.Color

However I've been unable to implement it in the above block of VBA. I tried replacing Interior.Color with different parts of it and none seem to work. Does anyone know a simple way to do this?

Thank you in advance!

Rory Rory · Accepted Answer · 2014-09-18T15:15:48

Since you have 2010, you can use the DisplayFormat property:

        For i = 1 To UBound(vntValues)
            myseries.Points(i).Interior.Color = Range(s).Cells(i).DisplayFormat.Interior.Color
        Next i

Excel VBA to fill pie chart colors from cells with conditional formatting

1 Answers