MS Chart

[sogibear]

Hi,
I'm using an array to hold data for my chart like so.
arrPrices(1) = 1
arrPrices(2) = 2
arrPrices(3) = 3
arrPrices(4) = 4
arrPrices(5) = 5
arrPrices(6) = 6
i then call the chart like this :
ChWasherProfile.ChartData = arrPrices

No probs so far, however i need to set the color of each Column in the chart to a specific color, how do i go about doing this ?

please help
Thanks

 

[avrcan]

' Take look at following sample

Sub Chart_Sample()
Dim SeriesCollVal, SeriesCollXVal()
Dim ForeSchemeColor, BackSchemeColor, i As Integer

' Fill arrays
ForeSchemeColor = Array(11, 9, 51, 53, 49, 9)
BackSchemeColor = Array(5, 46, 43, 44, 42, 3)

ReDim SeriesCollVal(5)
ReDim SeriesCollXVal(5)
For i = 1 To 6
SeriesCollVal(i - 1) = i
SeriesCollXVal(i - 1) = "Price" & i
Next i

Application.ScreenUpdating = False

' delete all charts
Delete_Charts

' create new chart
Charts.Add
' define Chart Type and Location
With ActiveChart
.ChartType = xlColumnClustered
.Location Where:=xlLocationAsObject, Name:="Sheet1"
End With
With ActiveChart
' .SetSourceData _
Source:=Sheets(1).Range("A1:B7&quot, _
PlotBy:=xlColumns
.SeriesCollection(1).Values = SeriesCollVal
.SeriesCollection(1).XValues = SeriesCollXVal
.HasTitle = True

' Link Chart Title with cell "D1"
With .ChartTitle
.Text = "=Sheet1!R1C4" ' row=1, column=4 --> "D1"
With .Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 14
.ColorIndex = 11
End With
End With
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With

' define series borders and colors
For i = 1 To 6
With ActiveChart.SeriesCollection(1).Points(i)
With .Border
.ColorIndex = 2
.Weight = xlThin
.LineStyle = xlContinuous
End With
.Fill.TwoColorGradient Style:=msoGradientVertical, Variant:=3
With .Fill
.Visible = True
.ForeColor.SchemeColor = ForeSchemeColor(i - 1)
.BackColor.SchemeColor = BackSchemeColor(i - 1)
End With
End With
Next i

With ActiveChart
' format PlotArea
With .PlotArea
With .Border
.ColorIndex = 37
.Weight = xlThin
.LineStyle = xlContinuous
End With
With .Fill
.TwoColorGradient _
Style:=msoGradientDiagonalDown, Variant:=4
.Visible = True
.ForeColor.SchemeColor = 2
.BackColor.SchemeColor = 37
End With
End With
' format MajorGridlines
With .Axes(xlValue).MajorGridlines.Border
.ColorIndex = 15
.Weight = xlHairline
.LineStyle = xlContinuous
End With

' format Legend
With .Legend
With .Border
' .Weight = xlHairline
.LineStyle = xlNone
End With
With .Font
.Name = "Arial"
.Size = 10
.ColorIndex = 11
End With
End With
End With

Range("A1&quot.Select
' andrija.vrcan@dalmacijacement.hr
End Sub