Colour coding bar of graphs

Hi Rhian

Hope you are well. Here's a way to colour a column chart according to the type of item (in this case fruit).

Say the data looks like this -

Fruit Quantity sold
Pears 200
Oranges 120
Apples 70
Bananas 230
Blueberries 30

Here's the vba code. See if you can adapt it for your example. It assumes the chart already exists and called Chart1.

Dim PointNo As Integer
Dim NumItems As Integer
Dim Items(20) As String ' an array variable of 20 items

Dim n As Integer
n = 1

' count number of items

NumItems = Range("A1").CurrentRegion.Rows.Count - 1

'Add list of items to an array variable
For n = 1 To NumItems
Items(n) = Range("a2").Cells(n, 1).Value
Next n

ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.FullSeriesCollection(1).Select

'select the colours one by one
For PointNo = 1 To NumItems

ActiveChart.FullSeriesCollection(1).Point(PointNo).Select
Select Case Items(PointNo)
Case Is = "Apples"
Selection.Format.Fill.ForeColor.RGB = RGB(255, 0, 0)
Case Is = "Pears"
Selection.Format.Fill.ForeColor.RGB = RGB(0, 255, 0)
Case Is = "Oranges"
Selection.Format.Fill.ForeColor.RGB = RGB(255,192, 0)
Case Is = "Bananas"
Selection.Format.Fill.ForeColor.RGB = RGB(255, 255, 0)
Case is = "Blueberries"
Selection.Format.Fill.ForeColor.RGB = RGB(0, 0, 255)
Case Else
Selection.Format.Fill.ForeColor.RGB = RGB(255, 255, 255)

End Select

Next PointNo

Range("A1").Select
End Sub

Let me know how you get on!

Doug Dunn
Best STL