Author: Oscar Cronquist Article last updated on January 09, 2018

Peter asks:

I’m new working with dynamic charts using Excel 2007. I created a dynamic bar chart using 2 series of yearly sales. I have defined range names FW for series 1 and SS for Series 2. I would like to display each respective bar from each series in blue each time sales increase from the previous year and in red each time the sales decrease from previous year.
How would I go about doing this?


I created a table (Table2), column Year must be the first column.

Add the following vba code to the sheet module.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Single, c As Single
If Not Intersect(Target, Range("Table2")) Is Nothing Then
    On Error Resume Next
    For c = 2 To Range("Table2").Columns.Count
        For r = 1 To Range("Table2").Rows.Count
            With ActiveSheet.ChartObjects(1).Chart.SeriesCollection(c - 1).Points(r).Interior
            If r = 1 Then
                .ColorIndex = 23
                If Range("Table2").Cells(r, c).Value >= Range("Table2").Cells(r - 1, c) Then
                    .ColorIndex = 23
                    .ColorIndex = 3
                End If
            End If
            End With
        Next r
    Next c
    On Error GoTo 0
End If
End Sub

Right click on sheet name and click "View Code". Copy above code and paste to sheet module.
Every time you change a value in the table, the chart bars are colored.

Recommended reading

Color Coded Bar Charts with Microsoft Excel

Download excel *.xlsm file

Colored bar chart.xlsm