Wednesday, 30 January 2013

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:

1 comment:

  1. is it possible to get charts color from source cell that has conditional formatting? i want the color formatted on the cell to be on the graph as well. thank you.