VBA Chart series color based on cell color

Akbarov

Board Regular
Joined
Jun 30, 2018
Messages
240
Office Version
  1. 365
Platform
  1. Windows
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

VBA Code:
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
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
8,848
Office Version
  1. 2016
Platform
  1. Windows
Does this work for you ?
VBA Code:
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

Board Regular
Joined
Jun 30, 2018
Messages
240
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Dec 5, 2002
Messages
8,848
Office Version
  1. 2016
Platform
  1. Windows
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.
 
Solution

Akbarov

Board Regular
Joined
Jun 30, 2018
Messages
240
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Dec 5, 2002
Messages
8,848
Office Version
  1. 2016
Platform
  1. Windows
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 :
VBA Code:
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
 

Forum statistics

Threads
1,175,531
Messages
5,897,955
Members
434,688
Latest member
vi28

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top