Colouring chart data points automatically

Pauljj

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

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
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!
 
Upvote 0
Thank you Domenic, this works perfectly and so much more concise then my code, much appreciated
 
Upvote 0

Forum statistics

Threads
1,219,006
Messages
6,145,713
Members
450,635
Latest member
Rookie3510

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top