J
J.W. Aldridge
Hi all.
I have the following macro that i recorded. (turns one bar green, one
yellow)
It changes the color for my pivot chart to the colors i want.
Instead of this long macro, I need one that says:
For every odd number bar in my pivot chart, make it green.
Every even number bar in my pivot chart, make it yellow.
This way, when I add new bars for the next months, I wont have to
re-record a new macro for each bar in my chart.
thanx
Sub color_changes()
'
' color_changes Macro
' Macro recorded 12/28/2006 by joe
'
' Keyboard Shortcut: Ctrl+q
'
ActiveChart.SeriesCollection(1).Select
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(1).Points(1).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
Selection.Shadow = False
Selection.InvertIfNegative = False
With Selection.Interior
.ColorIndex = 10
.Pattern = xlSolid
End With
ActiveChart.SeriesCollection(1).Points(2).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
Selection.Shadow = False
Selection.InvertIfNegative = False
With Selection.Interior
.ColorIndex = 36
.Pattern = xlSolid
End With
ActiveChart.SeriesCollection(1).Points(3).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
Selection.Shadow = False
Selection.InvertIfNegative = False
With Selection.Interior
.ColorIndex = 10
.Pattern = xlSolid
End With
ActiveChart.SeriesCollection(1).Points(4).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
Selection.Shadow = False
Selection.InvertIfNegative = False
With Selection.Interior
.ColorIndex = 36
.Pattern = xlSolid
End With
ActiveChart.SeriesCollection(1).Points(5).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
Selection.Shadow = False
Selection.InvertIfNegative = False
With Selection.Interior
.ColorIndex = 10
.Pattern = xlSolid
End With
ActiveChart.SeriesCollection(1).Points(6).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
Selection.Shadow = False
Selection.InvertIfNegative = False
With Selection.Interior
.ColorIndex = 36
.Pattern = xlSolid
End With
ActiveChart.SeriesCollection(1).Points(7).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
Selection.Shadow = False
Selection.InvertIfNegative = False
With Selection.Interior
.ColorIndex = 10
.Pattern = xlSolid
End With
ActiveChart.SeriesCollection(1).Points(8).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
Selection.Shadow = False
Selection.InvertIfNegative = False
With Selection.Interior
.ColorIndex = 36
.Pattern = xlSolid
End With
ActiveChart.SeriesCollection(1).Points(9).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
Selection.Shadow = False
Selection.InvertIfNegative = False
With Selection.Interior
.ColorIndex = 10
.Pattern = xlSolid
End With
ActiveChart.SeriesCollection(1).Points(10).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
Selection.Shadow = False
Selection.InvertIfNegative = False
With Selection.Interior
.ColorIndex = 36
.Pattern = xlSolid
End With
ActiveChart.SeriesCollection(1).Points(11).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
Selection.Shadow = False
Selection.InvertIfNegative = False
With Selection.Interior
.ColorIndex = 10
.Pattern = xlSolid
End With
ActiveChart.SeriesCollection(1).Points(12).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
Selection.Shadow = False
Selection.InvertIfNegative = False
With Selection.Interior
.ColorIndex = 36
.Pattern = xlSolid
End With
End Sub
I have the following macro that i recorded. (turns one bar green, one
yellow)
It changes the color for my pivot chart to the colors i want.
Instead of this long macro, I need one that says:
For every odd number bar in my pivot chart, make it green.
Every even number bar in my pivot chart, make it yellow.
This way, when I add new bars for the next months, I wont have to
re-record a new macro for each bar in my chart.
thanx
Sub color_changes()
'
' color_changes Macro
' Macro recorded 12/28/2006 by joe
'
' Keyboard Shortcut: Ctrl+q
'
ActiveChart.SeriesCollection(1).Select
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(1).Points(1).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
Selection.Shadow = False
Selection.InvertIfNegative = False
With Selection.Interior
.ColorIndex = 10
.Pattern = xlSolid
End With
ActiveChart.SeriesCollection(1).Points(2).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
Selection.Shadow = False
Selection.InvertIfNegative = False
With Selection.Interior
.ColorIndex = 36
.Pattern = xlSolid
End With
ActiveChart.SeriesCollection(1).Points(3).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
Selection.Shadow = False
Selection.InvertIfNegative = False
With Selection.Interior
.ColorIndex = 10
.Pattern = xlSolid
End With
ActiveChart.SeriesCollection(1).Points(4).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
Selection.Shadow = False
Selection.InvertIfNegative = False
With Selection.Interior
.ColorIndex = 36
.Pattern = xlSolid
End With
ActiveChart.SeriesCollection(1).Points(5).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
Selection.Shadow = False
Selection.InvertIfNegative = False
With Selection.Interior
.ColorIndex = 10
.Pattern = xlSolid
End With
ActiveChart.SeriesCollection(1).Points(6).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
Selection.Shadow = False
Selection.InvertIfNegative = False
With Selection.Interior
.ColorIndex = 36
.Pattern = xlSolid
End With
ActiveChart.SeriesCollection(1).Points(7).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
Selection.Shadow = False
Selection.InvertIfNegative = False
With Selection.Interior
.ColorIndex = 10
.Pattern = xlSolid
End With
ActiveChart.SeriesCollection(1).Points(8).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
Selection.Shadow = False
Selection.InvertIfNegative = False
With Selection.Interior
.ColorIndex = 36
.Pattern = xlSolid
End With
ActiveChart.SeriesCollection(1).Points(9).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
Selection.Shadow = False
Selection.InvertIfNegative = False
With Selection.Interior
.ColorIndex = 10
.Pattern = xlSolid
End With
ActiveChart.SeriesCollection(1).Points(10).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
Selection.Shadow = False
Selection.InvertIfNegative = False
With Selection.Interior
.ColorIndex = 36
.Pattern = xlSolid
End With
ActiveChart.SeriesCollection(1).Points(11).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
Selection.Shadow = False
Selection.InvertIfNegative = False
With Selection.Interior
.ColorIndex = 10
.Pattern = xlSolid
End With
ActiveChart.SeriesCollection(1).Points(12).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
Selection.Shadow = False
Selection.InvertIfNegative = False
With Selection.Interior
.ColorIndex = 36
.Pattern = xlSolid
End With
End Sub