Chart columns coloring automatically based on cell font colors in a row

[deacat]

Hi All,

I'm a rookie in VBA and I have an Excel VBA issue that is driving me crazy. I have created dynamic charts that select their data using an offset formula and a defined range. I want to color the columns of the charts according to the second row of the sheet, but the numer of columns needs to come from the data of the charts. I want to apply the same macro to all of the charts in a sheet, all sheets of the workbook, if possible, that all have the same structure, but the number of columns varies.

I tried to write the code, but (obviously), it's not working:

Sub ReColor()

    Dim cht As Chart
    Dim i As Integer
    Dim vntValues As Variant
    Dim s As String
    Dim j As Integer
    Dim s_new As Range
    Dim j_max As Integer
    Dim rColor As Range
    Dim xColumns As Long
    Dim ColumnLetter As String

j_max = ActiveSheet.ChartObjects.Count
    For j = 1 To j_max
        Set cht = ActiveSheet.ChartObjects(j).Chart
        With cht.SeriesCollection(1)
            Set s_new = ActiveSheet.Range(Split(Split(.Formula, ",")(1), "!")(1))
            xColumns = s_new.Columns.Count
            ColumnLetter = Split(Cells(1, xColumns + 1).Address, "$")(1)
    Set rColor = .Range("$B$2:" & ColumnLetter & "$2")
For i = 1 To xColumns
                .Points(i).Interior.Color = rgbBlack
                .Points(i).Format.Fill.BackColor.RGB = Range(rColor).Cells(i).Font.Color
          Next
        End With
    Next

End Sub

Any help would be highly appreciated. Thanks!

 

[deacat]

Anybody? Pls!