Stacked Bar Chart which uses colors referenced on another sheet

[allstarrunner]

Hi, I have had a terrible time trying to find the vba code I want, so I am hoping someone here can help me out:

I have a stacked bar chart which is referencing the data in colums A and B; what I what to do is have all of the colors on the stacked bar chart be different shades of one color. For example, starting at the bottom of the stacked bar and moving up I want it to go from a blue I specify to a ligher one, so each stacked element is a little more transparent or lighter blue. I can't do this manually, (at least I don't think), because the number of data points and "stacks" could differ (since this is built inside a macro which references a dynamic range). I'm thinking, but not smart enough to write the code, that if I could have the vba say something to the effect of "series 1 = color of cell A1 on "sheet 2", if Series 2, then use color A2 on "sheet 2", and so on.

I am sorry I don't have any code to show as I really have non idea where to start on this one....Any help would be greatly appreciated! Thanks!

 

[AlphaFrog]

This sets the each series interior color to the cell's Background colors from Sheet2 A1, A2, A3...

[COLOR=darkblue]Sub[/COLOR] Color_Chart_Series()
    [COLOR=darkblue]Dim[/COLOR] i [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    [COLOR=darkblue]If[/COLOR] [COLOR=darkblue]Not[/COLOR] ActiveChart [COLOR=darkblue]Is[/COLOR] [COLOR=darkblue]Nothing[/COLOR] [COLOR=darkblue]Then[/COLOR]
        [COLOR=darkblue]With[/COLOR] ActiveChart
            [COLOR=darkblue]For[/COLOR] i = 1 [COLOR=darkblue]To[/COLOR] .SeriesCollection.Count
                .SeriesCollection(i).Interior.Color = Sheets("Sheet2").Range("A" & i).Interior.Color
            [COLOR=darkblue]Next[/COLOR] i
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    [COLOR=darkblue]Else[/COLOR]
        MsgBox "First select a chart. ", , "No Chart Selected"
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
End [COLOR=darkblue]Sub[/COLOR]

 

[allstarrunner]

Thank you very much for this! Since I will be inserting this macro inside of another macro, would it be possible to alter it slightly to where it doesn't necisarilly look for the active chart but calls upon a chart by name? I have 3 charts that will have 3 different shades of coloring, so I would use the code you have above and it would reference "Chart1", and the colors in A1, etc.; then I would basiclaly use the code again except this time call on "Chart2" and then use the colors from B1, etc.? If not I will work with what I have which is a lot more than I had before! Thanks again!!

 

[allstarrunner]

I think I figured it out; I made it so it calls upon the colors as it (or after it) makes the graph:

Dim lMaxRow As Long
Dim i As Long
lMaxRow = Sheets("UM Analysis").Range("E100").End(xlUp).Row
With Sheets("UM Analysis").ChartObjects.Add(Left:=200, Width:=275, Top:=1000, Height:=325)
    .Name = "MyChart"
    With .Chart
        .SetSourceData Source:=Union(Sheets("Um Analysis").Range("J3:J" & lMaxRow), Sheets("UM Analysis").Range("M3:M" & lMaxRow))
        .ApplyChartTemplate ( _
        "C:\Users\Name\AppData\Roaming\Microsoft\Templates\Charts\StackedBox.crtx" _
        )
        .HasTitle = True
        .ChartTitle.Text = "3 & 4 Unit UM Breakdown"
        .PlotBy = xlRows
            For i = 1 To .SeriesCollection.Count
                .SeriesCollection(i).Interior.Color = Sheets("Sheet2").Range("A" & i).Interior.Color
            Next i
    End With
End With