Colouring chart data points automatically

Pauljj

Well-known Member
Joined
Mar 28, 2004
Messages
2,005
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

Code:
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
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,300
The Select Case statement can be used. Here's an example...

Code:
    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

Well-known Member
Joined
Mar 28, 2004
Messages
2,005
Thank you Domenic, this works perfectly and so much more concise then my code, much appreciated
 

Watch MrExcel Video

Forum statistics

Threads
1,108,987
Messages
5,526,068
Members
409,685
Latest member
Davetom

This Week's Hot Topics

Top