How to colour Excel chart according to source data using VBA

I will demonstrate you the power of Excel VBA macros with a nice simple macro that goes through all your chart objects and set the colours of those according to their source cells' colour. If you are unfamiliar with Excel VBA, then I suggest that you first check my previous post Excel Tips and Tricks: How to write simple VBA function.

Here is the code that does the trick (I don't guarantee that it works in every cases, but it should give you the idea):

Sub ColorCharts()

    For Each ch In ActiveSheet.ChartObjects

        For Each ser In ch.Chart.SeriesCollection

            s = Split(ser.Formula, ",")

            For i = 1 To UBound(ser.Values)

                ser.Points(i).Interior.Color = Range(s(2)).Cells(i).Interior.Color

            Next i

        Next ser

    Next ch

End Sub

Following video demonstrates this: