Color Chart Series by Cell Color without hidden Cells

Question

I am trying to create a chart using cell colors from the active cells on a specific worksheet. When using the macro provided below all work perfectly. The colors are match with the cells. BUT, when hide or filter some rows this macro no running correctly, why???? PLEASE HELP ME

Sub ColorChartbyCellColor()
With Sheets("Chart1").SeriesCollection(1)
Set vAddress = Sheets("Sheet1").Range(Split(Split(.Formula, ",")(1), "!")(1))
For i = 1 To vAddress.Cells.Count
    .Points(i).Format.Fill.ForeColor.RGB = ThisWorkbook.Colors(vAddress.Cells(i).Interior.ColorIndex)
    Next i
End With
End Sub

Answer 1

If you filter your data then your chart series has fewer points, but the source range is still the same size. So as you loop over vAddress you eventually run out of points to set the color on (and the ones you do set may be the wrong ones).

You need to only count visible rows when you're looping:

Sub ColorChartbyCellColor()

    Dim vAddress As Range, n As Long, c As Range

    With Sheets("Chart1").SeriesCollection(1)

        Set vAddress = Sheets("Sheet1").Range(Split(Split(.Formula, ",")(1), "!")(1))
        n = 0 'for counting visible datapoints
        For Each c In vAddress.Cells
            'is the row visible? skip if not
            If Not c.EntireRow.Hidden Then
                n = n + 1 'next visible datapoint
                .Points(n).Format.Fill.BackColor.RGB = c.Interior.Color
            End If
        Next c
    End With

End Sub