Excel VBA for changing marke size, color, and transparency on a radar plot

Question

I'm being stonewalled by VBA when attempting to assign a custom marker color AND transparency level on a radar chart. I've read there are some issues with order, but no matter where I try the .transparency parameter, I get the following error: Object doesn't support this property or method.

If I comment out the .transparency line in the following code, I get a great radar plot with markers colored by values in rngColors. I would just like to make them transparent so the underlying line plots come through as well. Any help or advice would be greatly appreciated.

Regards, Adam

Sub colorPoints()

'Must select chart when running macro

  Dim x As Long
  Dim rngColors As Range

  Set rngColors = Range("H8:H57") 'set range of RGB color

  For x = 1 To ActiveChart.SeriesCollection(1).Points.Count
    With ActiveChart.SeriesCollection(1).Points(x)

        .Format.Fill.Solid
        .MarkerBackgroundColor = RGB(212, 142, rngColors(x))
        .transparency = 0.5 <-Error: 'Object doesn't support this property or method.'

    End With
Next

End Sub

EDIT: Thanks to the link in the comments, the following code worked for me when run as a separate macro after assigning color. It's tricky though, and I don't know why. First I need to run the transparency code (below), then comment out .Solid, then run the color code (above), then the transparency code again (below), then it works. Yikes! I'm not too worried about optimizing now, but this seems to work regularly:

Sub transcheck()

' transcheck Macro

Dim cht As Chart
Dim Ser As Series
Dim lngIndex As Long
Dim lngChartType As XlChartType

Set cht = ActiveSheet.ChartObjects(1).Chart
Set Ser = cht.SeriesCollection(1)
lngChartType = Ser.ChartType
Ser.ChartType = xlColumnClustered

For lngIndex = 1 To 50
 With Ser.Format.Fill
   .Solid
   .Visible = True
   .transparency = 0.5
 End With

Ser.ChartType = lngChartType
Next
End Sub

Chrismas007 Chrismas007 · Accepted Answer · 2014-12-08T15:39:21

The information I found has proper syntax of .Format.Fill.Transparency = 0.5 http://answers.microsoft.com/en-us/office/forum/office_2007-excel/macro-to-change-the-transparency-of-markers-in-a/6a9964a7-30ad-4412-a48f-2334e4ecd63d

Although there is discussion of bugs with that coding depending on your Excel version: http://www.mediafire.com/file/j2tnzlcizzm/05_09_10b.pdf

Excel VBA for changing marke size, color, and transparency on a radar plot

1 Answers