Setting the Colour of Individual Items within a Legend

[Michael Hudston]

I am using the following Code to plot the chart, but Im not sure how to getit
to let me decide the colours of the Columns it displays

Sub Chrt_Incident_Age_Click()

Dim chtChart As Chart

' Remove Existing Chart

ActiveSheet.ChartObjects.Delete

' Create a new chart.

Set chtChart = Charts.Add
Set chtChart = chtChart.Location(Where:=xlLocationAsObject,
Name:="CHARTS")

With chtChart

.ChartType = xlCylinderCol

' Set data source range.

.SetSourceData Source:=Sheets("BASIC CHART DATA").Range("L11:X14"),
PlotBy:=xlRows
.HasTitle = True
.ChartTitle.Text = "Current Status"
.SeriesCollection(1).XValues = "='BASIC CHART DATA'!$M$4:$X$10"

'.SeriesCollection(1).XValues = "='BASIC CHART DATA'!$M$4:$X$10"

' The Parent property is used to set properties of the Chart.

With .Parent
.Top = Range("G3").Top
.Left = Range("G3").Left
.Width = Range("G3:R30").Width
.Height = Range("G3:R30").Height


End With

End With

ActiveChart.Legend.Select
Selection.Delete

End Sub

I tried the following code that was mentioned on here and it doesnot appear
to do anything. DOes anyone have any ideas?

For i = 1 To ActiveChart.Legend.LegendEntries.Count
ActiveChart.Legend.LegendEntries(i).LegendKey.Select

With Selection.Interior
Select Case i
Case 1
.Color = RGB(0, 51, 153)
Case 2
.Color = RGB(64, 102, 178)
Case 3
.Color = RGB(128, 153, 204)
Case 4
.Color = RGB(102, 102, 255)
Case 5
.Color = RGB(140, 140, 255)
Case 6
.Color = RGB(178, 178, 255)
Case 7
end select
Next

I hope this will help you!!!

Best regards,
Shiva

 

[Peter T]

Better to format the series themselves, legend formats will reflect same.

Dim sr As Series
Dim arr
Dim i

arr = Array(RGB(0, 51, 153), RGB(64, 102, 178), _
RGB(128, 153, 204), RGB(102, 102, 255), _
RGB(140, 140, 255), RGB(178, 178, 255))

i = -1
For Each sr In chtChart .SeriesCollection
i = i + 1
If i <= UBound(arr) Then sr.Interior.Color = arr(i)
Next

Unless you are using XL2007, with a default palette your interior formats
will not update to your particular RGB's. Instead they will match to the
closest matching colours that exist in the palette. If you want all your
charts in the workbook to adopt your colour sheme suggest you customize the
palette. In particular the bottom two rows of the palette.

Regards,
Peter T

 

[Michael Hudston]

Ok that is changin the colours, but they are all the same. One thing I have
identified is that they all appear to e points in the same series, which is
why they all have the same colour.

 

[Peter T]

I don't follow, what are "e points in the same series". Also I don't
understand the final conclusion at all.

Regards,
Peter T

 

[Michael Hudston]

Peter,

Sorry, was a typo on my part, and also I think I have confused the issue.

Tha data is in a single series, and they are points in the series. I am
trying to set the colour of individual points, which each represent single
columns in the chart.

I could use,

ActiveChart.SeriesCollection(1).Points(x).Interior.Color = RGB(red, green,
blue) where x is the point umber (From 1 to 6 in this case).

 

[Peter T]

OK understood. Looks like you know what to do but could change the example I
gave

Dim pt as point
'code
For Each pt In chtChart.SeriesCollection(1).Points
' etc

Regards,
Peter T

 

[Michael Hudston]

THanks Peter, Ill give that a go, and sorry for the confusion.