Colouring chart data points automatically

Pauljj

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

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
20,224
Office Version
  1. 365
Platform
  1. Windows
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,046
Thank you Domenic, this works perfectly and so much more concise then my code, much appreciated
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,017
Messages
5,834,960
Members
430,330
Latest member
drAli77

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
Top