VOHLC Chart - Change Colour of Volume Bars

[gregga73]

I have several worksheets - one stock per worksheet containing all the necessary data to construct a VOHLC chart embedded in each worksheet.
This all works fine, however, I cannot work out how to change the volume bars from the current dark blue to orange. I know how to do it manually but not as a macro.

I thought that if I recorded a macro to change the colour in volume bars (and also to change the gap width) then this would work, but when I paste it into my existing macro it does not work:

ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(1).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
Selection.Shadow = False
Selection.InvertIfNegative = False
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
With ActiveChart.ChartGroups(1)
.Overlap = 0
.GapWidth = 0
.HasSeriesLines = False
.VaryByCategories = False
End With





The code to actually create the chart works fine (below), including changing the "GapWidth" between the bars, so it seems that something like .chart.chartgroups(1).colorindex = 6 would do the trick, but it does not work.


Set cht = ActiveSheet.ChartObjects.Add _
(Left:=10, Width:=1000, Top:=10, Height:=400)
With cht
.Chart.SetSourceData Source:=Sheets(str).Range("C1", Range("G1").End(xlDown))
.Chart.ChartType = xlStockVOHLC
.Chart.HasTitle = True
.Chart.ChartTitle.Text = str
.Chart.ChartGroups(1).GapWidth = 10
.Chart.ChartGroups(2).GapWidth = 5
End With

I am using excel 2003. Any ideas?

 

[gregga73]

After some mucking about I found that ".Chart.SeriesCollection(1).Interior.ColorIndex = 6" did the trick. It's actually yellow, but good enough for now.