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!

 

[BobBridges]

Normally I would ask you to describe exactly what's going wrong—you're getting an error message, or wrong results, or what?—but I broke down this code and I see a couple of obvious things. Mind you, I may have broken it down incorrectly. But there are a lot of tasks packed up into a few lines, so I thought it might help to break it up into pieces to be sure I followed it. Like this:

nZ = ActiveSheet.ChartObjects.Count
For jo = 1 To nZ
  Set och = ActiveSheet.ChartObjects(jo).Chart

  ' With cht.SeriesCollection(1)
  Set ose = och.SeriesCollection(1)

  ' Set s_new = ActiveSheet.Range(Split(Split(.Formula, ",")(1), "!")(1))
  ar = Split(ose.Formula, ",")
  vv = ar(1) '2nd comma-delimited string
  ar = Split(vv, "!") 'break up by '!'
  vv = ar(1) '2nd bang-delimited string
  Set org = ActiveSheet.Range(vv) 'where vv apparently is a Range description

  ncol = org.Columns.Count 'number of columns n the range

  ' ColumnLetter = Split(Cells(1, xColumns + 1).Address, "$")(1)
  Set oc = cells(1, ncol+1) 'header row, last column plus 1
  va = oc.Address
  ar = Split(va,"$") 'break up the address by '$'
  vv = ar(1) 'second $-delimited string

  ' Set rColor = .Range("$B$2:" & ColumnLetter & "$2")
  vr = "$B$2:" & ColumnLetter & "$2"
  Set org = ose.Range(vr)

  For jc = 1 To ncol
    ose.Points(jc).Interior.Color = rgbBlack
    ose.Points(jc).Format.Fill.BackColor.RGB = Range(org).Cells(i).Font.Color
    Next
  Next

If that's a correct breakdown of your code—and it may not be—one obvious problem I see is "Cells(i)" near the end. Even assuming it's looking at the Cells of the correct worksheet, don't you have to specify two numbers there, the row and the column?

And once you've define rColor as a range, I'm not comfortable with then saying Range(rColor). Seems redundant at best; maybe fatally so.

Better you should tell us exactly what's going wrong. You're getting an error message? What message, exactly—and where in the program is it happening?

 

[Hui]

Deacat
The best way to get a good response is to post a sample file with your post