I am creating a tool to automatically produce a chart.
I am using Excel 2007, btw.
One of the series in the chart needs to be semi-transparent as the markers will always be on top of another important series. The size of the markers will be different depending on the data and sometimes they will completely cover up the series underneath, which is why I need the marker to be semi-transparent. This is easy enough to do manually but I am unable to get the markers to go transparent in VBA. 
Here is some code (not the actual code I am using) that demonstrates my problem. I have inserted a break where the problem occurs. (You might not be able to run this without Excel 2007)
Sub test()
Dim myChtObj As ChartObject, chtSeries As Series
Set myChtObj = Sheets(1).ChartObjects.Add _
(Left:=Cells(1, 2).Left, Width:=Range(“B1:L1”).Width, Top:=Cells(3, 2).Top, Height:=Range(“A2:A22”).Height)
With myChtObj.Chart
Set chtSeries = .SeriesCollection.NewSeries
With chtSeries
.Values = Array(10, 30, 50, 20)
.XValues = Array(21, 32, 34, 32)
.ChartType = xlXYScatter
.MarkerStyle = xlMarkerStyleCircle
Dim Pnt As Point, i As Long
i = 2
For Each Pnt In chtSeries.Points
Pnt.MarkerSize = 5 + i
i = i + 2
Next Pnt
.MarkerBackgroundColor = RGB(255, 255, 0)
.MarkerForegroundColor = RGB(255, 0, 0)
Debug.Assert False
.Format.Fill.Transparency = 0.5
End With
End With
End Sub
When I try to set the transparency, Excel simply resets the fill format to automatic. :dubious: You can see this by manually setting the transparency and watching the behaviour as you execute the offending line of code.
Any help on this will be greatly appreciated.