VBA Chart series color based on cell color

[Akbarov]

Hello Dear community,

I want to know is there a way to make my chart series color dynamic?
So let's say A2:T2 are cells with different colors.
I want My Series 1 to change it is color based on cell A2 color
My Series 2 to change it is color based on cell B2 color and so on

I tried this code. But it fills all series with single color

Sub ColorAllItems()

Dim chrt As Chart
    Set chrt = Sheet1.ChartObjects(1).Chart
                  
    'We grab the color of the A1 on sheet 1
    '   then convert the color to RGB which is required for charts
    clr = Sheet1.Cells(1, 1).Interior.Color
                r = clr Mod 256
                g = clr \ 256 Mod 256
                b = clr \ 65536 Mod 256
    'The loop below loops through the all Axis points
    
    For c = 1 To chrt.SeriesCollection(1).Points.Count
        chrt.SeriesCollection(1).Points(c).Format.Fill.ForeColor.RGB = RGB(r, g, b)
    Next
End Sub

 

[Jaafar Tribak]

Does this work for you ?

Sub ColorAllItems()

    Dim chrt As Chart, i As Long, clr As Long
    Dim r As Byte, g As Byte, b As Byte
  
    Set chrt = Sheet1.ChartObjects(1).Chart
                
    For i = 1 To chrt.SeriesCollection.Count
        clr = Sheet1.Cells(2, i).Interior.Color
        r = clr Mod 256
        g = clr \ 256 Mod 256
        b = clr \ 65536 Mod 256
        chrt.SeriesCollection(i).Format.Fill.ForeColor.RGB = RGB(r, g, b)
     Next i
  
End Sub

 

[Akbarov]

Thanks for reply I will try now. But just a question. There are several charts in my worksheet. How do I select one of them in your code?

 

[Jaafar Tribak]

Thanks for reply I will try now. But just a question. There are several charts in my worksheet. How do I select one of them in your code?

In your code , you are working on the first chart in the following line:
Set chrt = Sheet1.ChartObjects(1).Chart
You are referring to the chart by its index (in this case index 1) but you can also refer to it by its name.
So, for example, if you want to work on the second chart, you will change the above line to :
Set chrt = Sheet1.ChartObjects(2).Chart ... and so on for the remaining charts.
By the way, you don't need to select the chart in order to work with it in code.

 

[Akbarov]

Thank you very much for detailed explanation. Last question. How can I change colored cells range in your code? I need to range it to F68 to Y68

 

[Jaafar Tribak]

Thank you very much for detailed explanation. Last question. How can I change colored cells range in your code? I need to range it to F68 to Y68

Something like this :

Sub ColorAllItems()

    Dim chrt As Chart, i As Long, clr As Long
    Dim r As Byte, g As Byte, b As Byte
    
    Set chrt = Sheet1.ChartObjects(1).Chart
                  
    For i = 1 To chrt.SeriesCollection.Count
        clr = Sheet1.Cells(68, i + 5).Interior.Color
        r = clr Mod 256
        g = clr \ 256 Mod 256
        b = clr \ 65536 Mod 256
        chrt.SeriesCollection(i).Format.Fill.ForeColor.RGB = RGB(r, g, b)
     Next i
    
End Sub