My data set consists of the financial impacts over time of a number of transactions, each sponsored by one or more banks. I am plotting the data in a stacked column chart, where the horizontal axis shows the period (i.e., 1Q97, 2Q97, etc.) and the vertical axis shows the dollar value of the transactions impacts. For each period I want to show a stacked column, where each column represents the financial impacts of all transactions in that quarter. However, I want the color of the column area representing each transaction to correspond to the bank that sponsored that transaction (where each bank is assigned a unique color). In the legend, I want to display the color assigned to each bank. I managed to assemble the following VBA macro (from some posts on this site) which changes the color of the series according to the series name. However, there are a few problems with how the macro works that I would like to fix, but I can't quite figure out how.
Problems:
(1) Because of how the data is organized, the impacts of each transaction associated with each bank represents one series. The problem is that each bank sponsored multiple transactions. I want the series name (and therefore its color) to correspond to the bank sponsor. Therefore there are currently multiple series with the same name. The current code assigns the proper color to the first series with each bank’s name, but does not change the color of any additional series with the same name (i.e., additional transactions sponsored by the same bank). I need to modify the code to change the color of the each series according to its name, and to apply the same color to all series with the same name.
(2) If possible, I’d like the legend to display only one series for each bank’s name (i.e., when there are multiple series with the same name, only display the first occurrence of that name). If this is not possible I can do this part manually.
(3) If possible, I’d like to change the code to apply only to the active chart instead of to all charts in the workbook.
Thanks in advance for your help.
Here’s the code as I have it now:
Sub LoopSetSeriesColors()
Dim WS As Worksheet
Dim CS As Chart
Dim CO As ChartObject
Dim sColors As Variant
Dim SeriesNames As Variant
Dim S As Series
Dim iSer As Integer
'Hard-wired series names and color values
SeriesNames = Array("Bank1", "Bank2", "Bank3", "Bank4", "Bank5", "Bank6", "Bank7", "Bank8", "Bank9", "Bank10", "Bank11", "Bank12", "Bank13", "Bank14")
sColors = Array(37, 36, 40, 39, 3, 44, 43, 38, 15, 35, 33, 42, 46, 5)
'loop through all charts on all worksheets
For Each WS In Worksheets
For Each CO In WS.ChartObjects
Set CS = CO.Chart
On Error Resume Next
For iSer = 1 To UBound(SeriesNames)
Set S = Nothing
Set S = CS.SeriesCollection(SeriesNames(iSer))
If Not S Is Nothing Then
With S.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
S.Shadow = False
S.InvertIfNegative = False
With S.Interior
.ColorIndex = sColors(iSer)
.Pattern = xlSolid
End With
End If
Next iSer
On Error GoTo 0
Next CO
Next WS
'loop thru all chart sheets
For Each CS In Charts
On Error Resume Next
For iSer = 1 To UBound(SeriesNames)
Set S = Nothing
Set S = CS.SeriesCollection(SeriesNames(iSer))
If Not S Is Nothing Then
With S.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
S.Shadow = False
S.InvertIfNegative = False
With S.Interior
.ColorIndex = sColors(iSer)
.Pattern = xlSolid
End With
End If
Next iSer
On Error GoTo 0
Next CS
End Sub
Problems:
(1) Because of how the data is organized, the impacts of each transaction associated with each bank represents one series. The problem is that each bank sponsored multiple transactions. I want the series name (and therefore its color) to correspond to the bank sponsor. Therefore there are currently multiple series with the same name. The current code assigns the proper color to the first series with each bank’s name, but does not change the color of any additional series with the same name (i.e., additional transactions sponsored by the same bank). I need to modify the code to change the color of the each series according to its name, and to apply the same color to all series with the same name.
(2) If possible, I’d like the legend to display only one series for each bank’s name (i.e., when there are multiple series with the same name, only display the first occurrence of that name). If this is not possible I can do this part manually.
(3) If possible, I’d like to change the code to apply only to the active chart instead of to all charts in the workbook.
Thanks in advance for your help.
Here’s the code as I have it now:
Sub LoopSetSeriesColors()
Dim WS As Worksheet
Dim CS As Chart
Dim CO As ChartObject
Dim sColors As Variant
Dim SeriesNames As Variant
Dim S As Series
Dim iSer As Integer
'Hard-wired series names and color values
SeriesNames = Array("Bank1", "Bank2", "Bank3", "Bank4", "Bank5", "Bank6", "Bank7", "Bank8", "Bank9", "Bank10", "Bank11", "Bank12", "Bank13", "Bank14")
sColors = Array(37, 36, 40, 39, 3, 44, 43, 38, 15, 35, 33, 42, 46, 5)
'loop through all charts on all worksheets
For Each WS In Worksheets
For Each CO In WS.ChartObjects
Set CS = CO.Chart
On Error Resume Next
For iSer = 1 To UBound(SeriesNames)
Set S = Nothing
Set S = CS.SeriesCollection(SeriesNames(iSer))
If Not S Is Nothing Then
With S.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
S.Shadow = False
S.InvertIfNegative = False
With S.Interior
.ColorIndex = sColors(iSer)
.Pattern = xlSolid
End With
End If
Next iSer
On Error GoTo 0
Next CO
Next WS
'loop thru all chart sheets
For Each CS In Charts
On Error Resume Next
For iSer = 1 To UBound(SeriesNames)
Set S = Nothing
Set S = CS.SeriesCollection(SeriesNames(iSer))
If Not S Is Nothing Then
With S.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
S.Shadow = False
S.InvertIfNegative = False
With S.Interior
.ColorIndex = sColors(iSer)
.Pattern = xlSolid
End With
End If
Next iSer
On Error GoTo 0
Next CS
End Sub
