Colouring chart data points automatically

[Pauljj]

Morning, I have a list of 35 shops and their sales, the chart updates weekly and the list of shops re-orders according to their sales position

The ordering of the shops is based on a formula, using the LARGE function

Some of the shops need to have their data point in a different colour.

Up to now I have used a rather unwieldy way of coding this but believe there may well be a better way....part of the code I am using....to give you an example is

If Range("AI3") = 1 Then

Sheets("AWPI").ChartObjects("Chart 1").Chart.SeriesCollection(1).Points(1).Interior.Color = RGB(204, 155, 255)

Else

Sheets("AWPI").ChartObjects("Chart 1").Chart.SeriesCollection(1).Points(1).Interior.Color = RGB(202, 255, 151)


End If

    If Range("AI4") = 1 Then

Sheets("AWPI").ChartObjects("Chart 1").Chart.SeriesCollection(1).Points(2).Interior.Color = RGB(204, 155, 255)


Else

Sheets("AWPI").ChartObjects("Chart 1").Chart.SeriesCollection(1).Points(2).Interior.Color = RGB(202, 255, 151)

End If

If Range("AI5") = 1 Then

Sheets("AWPI").ChartObjects("Chart 1").Chart.SeriesCollection(1).Points(3).Interior.Color = RGB(204, 155, 255)


Else

Sheets("AWPI").ChartObjects("Chart 1").Chart.SeriesCollection(1).Points(3).Interior.Color = RGB(202, 255, 151)

End If

If Range("AI6") = 1 Then

This works but rather then having 2 colour options I want 3 and this as you can see will mean a huge amount more code.

Does anyone have any ideas of a better way please ?

Thanks

 

[Domenic]

The Select Case statement can be used. Here's an example...

    Dim colorCriteriaRange As Range
    Dim pointIndex As Long
    
    Set colorCriteriaRange = Range("AI3:AI6") 'adjust the range accordingly
    
    With Sheets("AWPI").ChartObjects("Chart 1").Chart.SeriesCollection(1)
        For pointIndex = 1 To .Points.Count
            Select Case colorCriteriaRange(pointIndex)
                Case 1
                    .Points(pointIndex).Interior.Color = RGB(204, 155, 255)
                Case 2
                    .Points(pointIndex).Interior.Color = RGB(202, 255, 151)
                Case Else
                    .Points(pointIndex).Interior.Color = RGB(189, 215, 189) 'change the color accordingly
            End Select
        Next pointIndex
    End With

Hope this helps!

 

[Pauljj]

Thank you Domenic, this works perfectly and so much more concise then my code, much appreciated