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 Else If Range("Table2").Cells(r, c).Value >= Range("Table2").Cells(r - 1, c) Then .ColorIndex = 23 Else .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.