Excel Chart Objects: Change Series Colors depending on Legend Labels
I just wrote a simple macro for changing Excel Chart Legend Format and Texts
'Apply styles depending on the Label of an Excel Chart Series
Sub apply_colors(ByRef cht As Chart)
Dim aXVals As Variant
Dim aVals As Variant
'read values
'Get all Legend Labels from the Chart being currently selected
aXVals = cht.SeriesCollection(1).XValues
'Get all Values from the Chart being currently selected
aVals = cht.SeriesCollection(1).Values
'loop through chart
For i = 1 To cht.SeriesCollection(1).Points.Count
'Select Chart Point
cht.SeriesCollection(1).Points(i).Select
'and Format the color of the chart
Selection.Format.Fill.ForeColor.RGB = GetColor(aXVals(i))
DoEvents
Next
End Sub
'Global Macro for the Main loop through all Excel Sheets and Chart objects
Sub apply_styles()
'loop through sheets
For i = 1 To ActiveWorkbook.Sheets.Count
ActiveWorkbook.Sheets(i).Activate
For j = 1 To ActiveWorkbook.Sheets(i).ChartObjects.Count
apply_colors ActiveWorkbook.Sheets(i).ChartObjects(j).Chart
Next
DoEvents
Next
End Sub
'Get the color for an Excel Chart Series
Function GetColor( ByVal legend_name ) as String
Select Case IdentName
Case "Your Legend Label 1"
GetColor = RGB( 0 , 0 , 255)
Exit Function
Case "Your Legend Label 2"
GetColor = RGB( 255 , 0 , 255)
Exit Function
Case "Your Legend Label 3"
GetColor = RGB( 255 , 255 , 255)
Exit Function
End Select
End Function
'Apply styles depending on the Label of an Excel Chart Series
Sub apply_colors(ByRef cht As Chart)
Dim aXVals As Variant
Dim aVals As Variant
'read values
'Get all Legend Labels from the Chart being currently selected
aXVals = cht.SeriesCollection(1).XValues
'Get all Values from the Chart being currently selected
aVals = cht.SeriesCollection(1).Values
'loop through chart
For i = 1 To cht.SeriesCollection(1).Points.Count
'Select Chart Point
cht.SeriesCollection(1).Points(i).Select
'and Format the color of the chart
Selection.Format.Fill.ForeColor.RGB = GetColor(aXVals(i))
DoEvents
Next
End Sub
'Global Macro for the Main loop through all Excel Sheets and Chart objects
Sub apply_styles()
'loop through sheets
For i = 1 To ActiveWorkbook.Sheets.Count
ActiveWorkbook.Sheets(i).Activate
For j = 1 To ActiveWorkbook.Sheets(i).ChartObjects.Count
apply_colors ActiveWorkbook.Sheets(i).ChartObjects(j).Chart
Next
DoEvents
Next
End Sub
'Get the color for an Excel Chart Series
Function GetColor( ByVal legend_name ) as String
Select Case IdentName
Case "Your Legend Label 1"
GetColor = RGB( 0 , 0 , 255)
Exit Function
Case "Your Legend Label 2"
GetColor = RGB( 255 , 0 , 255)
Exit Function
Case "Your Legend Label 3"
GetColor = RGB( 255 , 255 , 255)
Exit Function
End Select
End Function
Comments
Write the first comment
Leave a trace
Post being viewed right now
