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:


Leave a Reply

Your email address will not be published. Required fields are marked *