Conditional Formatting Not Recognized By Macro That Automates Chart Colour

[Lim Aina]

Hi guys,

I have a macro that automatically updates my doughnut chart colours to the original data cell colour. However, I have now inserted a conditional formatting to the original data cell which renders this macro unusable now. Please help!

Example, when users select the project progress in a drop down box in Planned Project List, cell D4 to ACTIVE, the cell F15 in Scratchpad will turn GREEN. I want the GREEN in cell F15 in Scratchpad to automatically turn the doughnut chart for Project 1 in Dashboard GREEN too. (Note: click closer to the text Project 1 in Dashboard when wanting the macro to automate the colour change).

Previously, without the conditional formatting set in the original data cell e.g. cell F15 in Scratchpad, the following macro works brilliantly in automating the doughnut chart colour whenever there is a manual colour update to cell F15. Please help, thank you!

ColourDoughnut Macro:

Sub ColourDoughnut()
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 <> xlDoughnut Then GoTo SkipNotDoughnut
            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
SkipNotDoughnut:
        Next myseries
    Next cht
End Sub

 

[NARAYANK991]

Hi Lim ,

Detecting a cell colour which is due to CF involves somewhat complicated code ; it is easier to use the logic in the CF rule to colour the charts.

Narayan