macro for pivot table

[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

 

[Jon Peltier]

Select the chart and run this code:

Sub AlternateColors()
Dim iPt As Long
With ActiveChart
For iPt = 1 To .SeriesCollection(1).Points.Count
If iPt Mod 2 = 0 Then
' even number - yellow
.SeriesCollection(1).Points(iPt).Interior.ColorIndex = 36
Else
' odd number - green
.SeriesCollection(1).Points(iPt).Interior.ColorIndex = 10
End If
Next
End With
End Sub

- Jon