Column Chart Colours Based on Horizontal Series Point Name

[DanMee]

I've searched for this, and can't find the right coding to get this to work!! Here is what I have:

ChartCol.Select        Charts.Add.NAME = "Option Average Scores"
        ActiveChart.ChartType = xlColumnClustered
        ActiveChart.Location Where:=xlLocationAsObject, NAME:="Visuals"
        Sheets("Visuals").Activate
        ActiveChart.Parent.Height = 250
        ActiveChart.Parent.Width = 400
        ActiveChart.Parent.Left = 830
        ActiveChart.Parent.Top = 585
        With ActiveChart.Axes(xlValue)
            .MaximumScale = 100
            .MinimumScale = 20
        End With
        ActiveChart.SeriesCollection(1).Points(1).Select
        If ActiveChart.SeriesCollection(1).Points(1).DataLabel.Text = "OPTION1" Then
        Else
            If ActiveChart.SeriesCollection(1).Points(1).DataLabel.Text = "OPTION2" Then
                With Selection.Format.Fill
                    .ForeColor.RGB = RGB(255, 0, 0)
                    .Solid
                End With
            Else
                With Selection.Format.Fill
                    .ForeColor.RGB = RGB(255, 102, 204)
                    .Solid
                End With
            End If
        End If
        ActiveChart.SeriesCollection(1).Points(2).Select
        If ActiveChart.SeriesCollection(1).Points(2).DataLabel.Text = "OPTION1" Then
        Else
            If ActiveChart.SeriesCollection(1).Points(2).DataLabel.Text = "OPTION2" Then
                With Selection.Format.Fill
                    .ForeColor.RGB = RGB(255, 0, 0)
                    .Solid
                End With
            Else
                With Selection.Format.Fill
                    .ForeColor.RGB = RGB(255, 102, 204)
                    .Solid
                End With
            End If
        End If
        ActiveChart.SeriesCollection(1).Points(3).Select
        If ActiveChart.SeriesCollection(1).Points(3).DataLabel.Text = "OPTION1" Then
        Else
            If ActiveChart.SeriesCollection(1).Points(3).DataLabel.Text = "OPTION2" Then
                With Selection.Format.Fill
                    .ForeColor.RGB = RGB(255, 0, 0)
                    .Solid
                End With
            Else
                With Selection.Format.Fill
                    .ForeColor.RGB = RGB(255, 102, 204)
                    .Solid
                End With
            End If
        End If
        ActiveChart.HasLegend = False
    End If

The error I get is in relation to the

If ActiveChart.SeriesCollection(1).Points(1).DataLabel.Text =

part

Method DataLabel of object Point failed.

Now i have since learnt that DataLabel isnt what i want. However I can't find the syntax for what I do want.

The Series will always have at least one of 3 options in it. Each with a specific colour attached. So my question is, How do I get the code to check the names on the horizontal axis and return the right result to the IF after the graph has been created?

 

[Domenic]

Try...

    Dim theChart As Chart
    Dim XValues As Variant
    Dim i As Long
    
    Set theChart = ActiveChart
    
    XValues = theChart.SeriesCollection(1).XValues
    
    For i = 1 To UBound(XValues)
        If XValues(i) = "OPTION1" Then
            'Do nothing
        Else
            If XValues(i) = "OPTION2" Then
                With theChart.SeriesCollection(1).Points(i)
                    With .Format.Fill
                        .ForeColor.RGB = RGB(255, 0, 0)
                        .Solid
                    End With
                End With
            Else
                With theChart.SeriesCollection(1).Points(i)
                    With .Format.Fill
                        .ForeColor.RGB = RGB(255, 102, 204)
                        .Solid
                    End With
                End With
            End If
        End If
    Next i

Hope this helps!

 

[DanMee]

Works a charm! Thanks!