Update bar colors on chart based on axis labels that change each month

Question

My chart measures factor returns placing the highest returning factor at the top of the chart. As the order may change on a monthly basis, I want to dynamically adjust the color based on the factor order.

I want to loop through each bar, match the factor name to the RGB code in my if statement block then move to the next bar.

What happens: The code starts on the first bar, correctly matches the bar to factor names color and moves on to the next cell instead of moving to next series.

Sub update_factor_barcharts()

    Dim cht As Chart
    Dim i As Long
    Dim c As Range
    Dim rng As Range

    Set cht = ActiveSheet.ChartObjects("Chart 7").Chart
    Set rng = ActiveSheet.Range("Q26:Q31")

    For i = 1 To cht.SeriesCollection.Count
        For Each c In rng.Cells
            If c = "Size" Then
                cht.SeriesCollection(1).Points(i).Interior.Color = RGB(Range("AS19").Value, Range("AT19").Value, Range("AU19").Value)
            ElseIf c = "Value" Then
                cht.SeriesCollection(1).Points(i).Interior.Color = RGB(Range("AS20").Value, Range("AT20").Value, Range("AU20").Value)
            ElseIf c = "Mom." Then
                cht.SeriesCollection(1).Points(i).Interior.Color = RGB(Range("AS21").Value, Range("AT21").Value, Range("AU21").Value)
            ElseIf c = "Low Vol." Then
                cht.SeriesCollection(1).Points(i).Interior.Color = RGB(Range("AS22").Value, Range("AT22").Value, Range("AU22").Value)
            ElseIf c = "Quality" Then
                cht.SeriesCollection(1).Points(i).Interior.Color = RGB(Range("AS23").Value, Range("AT23").Value, Range("AU23").Value)
            ElseIf c = "Div. Yield" Then
                cht.SeriesCollection(1).Points(i).Interior.Color = RGB(Range("AS24").Value, Range("AT24").Value, Range("AU24").Value)
            End If
        Next c
    Next i

End Sub

AssemblyRequired AssemblyRequired · Accepted Answer · 2019-11-20T19:37:54

Your code is moving to the next cell because once the series is found and color changed, the if statement ends and the loop forces it to Next c.

Two options:

Make an if statement for each series within the 'c' loop so that once the series is found and altered, it checks the next if statement.
Instead of the 'c' For loop, use 'select case'. Easier than using a bunch of if statements.

Update bar colors on chart based on axis labels that change each month

2 Answers