How to fill color of No Fill chart markers via VBA in Excel?-CodePudding

I have a number of charts in a excel file and each containing different series. On some series there are markers which are "No Fill" i.e. there is only border and no color. I want to change/fill color of only marker which are not filled already. I have made the following vba code but I am unable to understand why this code does not work. Here is my code:

Sub fillNoFillMarkers()
    Dim oChart As ChartObject
    Dim seriesIndex As Long
    Dim pt As Point
    Dim pointIndex As Long
    Dim srs As Series
    For Each oChart In ActiveSheet.ChartObjects
        oChart.Activate
        For Each srs In ActiveChart.SeriesCollection
            
                For pointIndex = 1 To srs.Points.Count
                    If srs.Points(pointIndex).Format.Fill.Visible = msoFalse Then
                        srs.Points(pointIndex).MarkerBackgroundColor = RGB(0, 100, 0)
                        srs.Points(pointIndex).MarkerForegroundColor = RGB(100, 0, 0)

                    End If
                Next pointIndex
            
        Next srs
    Next oChart
End Sub

CodePudding user response：

srs.Points(pointIndex).Format.Fill.Visible = msoFalse does not return False if the MarkerBackgroundColor or MarkerForegroundColor are not colored...

Please, test the next adapted code:

Sub fillNoFillMarkers()
    Dim oChart As ChartObject, seriesIndex As Long, pt As point
    Dim pointIndex As Long, srs As Series
    For Each oChart In ActiveSheet.ChartObjects
        oChart.Activate
        For Each srs In ActiveChart.SeriesCollection
                For pointIndex = 1 To srs.points.count
                        Set pt = srs.points(pointIndex)
    
                        If pt.MarkerBackgroundColor = -1 And pt.MarkerForegroundColor = -1 Then                                                                    
                            srs.points(pointIndex).MarkerBackgroundColor = RGB(0, 100, 0)
                            srs.points(pointIndex).MarkerForegroundColor = RGB(100, 0, 0)
                        End If
                Next pointIndex
        Next srs
    Next oChart
End Sub

I used Set pt = srs.points(pointIndex), only because the variable was already declared and doing that you can benefit of intellisense suggestions and make the code more compact...

CodePudding user response：

I was able to solve my problem with the help from FraneDuru. Here is my final Code:

Sub fillNoFillMarkers()
    Dim oChart As ChartObject, seriesIndex As Long, pt As Point
    Dim pointIndex As Long, srs As Series
    For Each oChart In ActiveSheet.ChartObjects
        oChart.Activate
        For Each srs In ActiveChart.SeriesCollection
                For pointIndex = 1 To srs.Points.Count
                        Set pt = srs.Points(pointIndex)
    
                        If pt.MarkerBackgroundColorIndex = xlNone Then
                            pt.MarkerBackgroundColor = RGB(100, 100, 0)
                            pt.MarkerForegroundColor = RGB(100, 0, 0)
                        End If
                Next pointIndex
        Next srs
    Next oChart
End Sub