Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro for pivot table
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro for pivot table
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.ColorInd ex = 36 Else ' odd number - green .SeriesCollection(1).Points(iPt).Interior.ColorInd ex = 10 End If Next End With End Sub - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "J.W. Aldridge" wrote in message ups.com... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot table macro | Excel Discussion (Misc queries) | |||
Pivot Table from Macro | Excel Discussion (Misc queries) | |||
Pivot Table by Macro? | Charts and Charting in Excel | |||
Pivot Table Macro Help | Excel Programming | |||
Steps from Macro recorder for Pivot table will not run as a Macro | Excel Programming |