Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
One Range, Two Colors
I am wondering if there is a way to get Excel to loop through a series of
data, build a chart based on this series of data, and alternate the colors of the bars on the chart. I guess the even points have to be a variable (i.e., Points(2).Select, Points(4).Select, etc.) This kind of a pain to do if looking at only one series. This is the macro that I recorded: Sub Macro1() ActiveSheet.ChartObjects("Chart 1").Activate ActiveChart.SeriesCollection(1).Select With Selection.Border .Weight = xlThin .LineStyle = xlAutomatic End With With Selection.Interior .ColorIndex = 3 .Pattern = xlSolid End With ActiveChart.SeriesCollection(1).Points(2).Select With Selection.Border .Weight = xlThin .LineStyle = xlAutomatic End With With Selection.Interior .ColorIndex = 11 .Pattern = xlSolid End With ActiveChart.SeriesCollection(1).Points(4).Select With Selection.Border .Weight = xlThin .LineStyle = xlAutomatic End With Selection.Shadow = False With Selection.Interior .ColorIndex = 11 .Pattern = xlSolid End With ActiveChart.SeriesCollection(1).Points(6).Select With Selection.Border .Weight = xlThin .LineStyle = xlAutomatic End With With Selection.Interior .ColorIndex = 11 End With ActiveChart.SeriesCollection(1).Points(8).Select With Selection.Border .Weight = xlThin .LineStyle = xlAutomatic End With With Selection.Interior .ColorIndex = 11 .Pattern = xlSolid End With ActiveChart.SeriesCollection(1).Points(10).Select With Selection.Border .Weight = xlThin .LineStyle = xlAutomatic End With With Selection.Interior .ColorIndex = 11 .Pattern = xlSolid End With ActiveChart.SeriesCollection(1).Points(12).Select With Selection.Border .Weight = xlThin .LineStyle = xlAutomatic End With With Selection.Interior .ColorIndex = 11 .Pattern = xlSolid End With ActiveChart.SeriesCollection(1).Points(14).Select With Selection.Border .Weight = xlThin .LineStyle = xlAutomatic End With Selection.Shadow = False Selection.InvertIfNegative = False With Selection.Interior .ColorIndex = 11 .Pattern = xlSolid End With End Sub Finally, there may be a way for the user to select the series; Im thinking of something like the following (which I found on this DG)€¦ Dim myRange As Range Dim myAdd As String Set myRange = Range("A1") myRange.Offset(0, 3) = ActiveSheet.Range("A1") Set myRange = Application.InputBox( _ "Select Range to link from", Type:=8) However, this doesnt work for some reason€¦ Any guidance would be much appreciated. Regards, Ryan--- -- RyGuy |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
One Range, Two Colors
In answer to your first question, this macro will loop through a series (in
this case series #1) and alternate the colors based on the colorindex numbers that you choose: Sub AlternateSeriesColors() Dim A As Integer Dim B As Integer A = 1 B = 2 Do On Error GoTo ErrHandler: ActiveChart.SeriesCollection(1).Points(A).Interior .ColorIndex = 4 ActiveChart.SeriesCollection(1).Points(B).Interior .ColorIndex = 7 A = A + 2 B = B + 2 Loop ErrHandler: Exit Sub End Sub ----------------- John Mansfield "ryguy7272" wrote: I am wondering if there is a way to get Excel to loop through a series of data, build a chart based on this series of data, and alternate the colors of the bars on the chart. I guess the even points have to be a variable (i.e., Points(2).Select, Points(4).Select, etc.) This kind of a pain to do if looking at only one series. This is the macro that I recorded: Sub Macro1() ActiveSheet.ChartObjects("Chart 1").Activate ActiveChart.SeriesCollection(1).Select With Selection.Border .Weight = xlThin .LineStyle = xlAutomatic End With With Selection.Interior .ColorIndex = 3 .Pattern = xlSolid End With ActiveChart.SeriesCollection(1).Points(2).Select With Selection.Border .Weight = xlThin .LineStyle = xlAutomatic End With With Selection.Interior .ColorIndex = 11 .Pattern = xlSolid End With ActiveChart.SeriesCollection(1).Points(4).Select With Selection.Border .Weight = xlThin .LineStyle = xlAutomatic End With Selection.Shadow = False With Selection.Interior .ColorIndex = 11 .Pattern = xlSolid End With ActiveChart.SeriesCollection(1).Points(6).Select With Selection.Border .Weight = xlThin .LineStyle = xlAutomatic End With With Selection.Interior .ColorIndex = 11 End With ActiveChart.SeriesCollection(1).Points(8).Select With Selection.Border .Weight = xlThin .LineStyle = xlAutomatic End With With Selection.Interior .ColorIndex = 11 .Pattern = xlSolid End With ActiveChart.SeriesCollection(1).Points(10).Select With Selection.Border .Weight = xlThin .LineStyle = xlAutomatic End With With Selection.Interior .ColorIndex = 11 .Pattern = xlSolid End With ActiveChart.SeriesCollection(1).Points(12).Select With Selection.Border .Weight = xlThin .LineStyle = xlAutomatic End With With Selection.Interior .ColorIndex = 11 .Pattern = xlSolid End With ActiveChart.SeriesCollection(1).Points(14).Select With Selection.Border .Weight = xlThin .LineStyle = xlAutomatic End With Selection.Shadow = False Selection.InvertIfNegative = False With Selection.Interior .ColorIndex = 11 .Pattern = xlSolid End With End Sub Finally, there may be a way for the user to select the series; Im thinking of something like the following (which I found on this DG)€¦ Dim myRange As Range Dim myAdd As String Set myRange = Range("A1") myRange.Offset(0, 3) = ActiveSheet.Range("A1") Set myRange = Application.InputBox( _ "Select Range to link from", Type:=8) However, this doesnt work for some reason€¦ Any guidance would be much appreciated. Regards, Ryan--- -- RyGuy |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
One Range, Two Colors
Build it as two series (stacked bars or columns), where one series has even
points and the other odd points. Format each series uniquely. No need for macros. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "ryguy7272" wrote in message ... I am wondering if there is a way to get Excel to loop through a series of data, build a chart based on this series of data, and alternate the colors of the bars on the chart. I guess the even points have to be a variable (i.e., Points(2).Select, Points(4).Select, etc.) This kind of a pain to do if looking at only one series. This is the macro that I recorded: Sub Macro1() ActiveSheet.ChartObjects("Chart 1").Activate ActiveChart.SeriesCollection(1).Select With Selection.Border .Weight = xlThin .LineStyle = xlAutomatic End With With Selection.Interior .ColorIndex = 3 .Pattern = xlSolid End With ActiveChart.SeriesCollection(1).Points(2).Select With Selection.Border .Weight = xlThin .LineStyle = xlAutomatic End With With Selection.Interior .ColorIndex = 11 .Pattern = xlSolid End With ActiveChart.SeriesCollection(1).Points(4).Select With Selection.Border .Weight = xlThin .LineStyle = xlAutomatic End With Selection.Shadow = False With Selection.Interior .ColorIndex = 11 .Pattern = xlSolid End With ActiveChart.SeriesCollection(1).Points(6).Select With Selection.Border .Weight = xlThin .LineStyle = xlAutomatic End With With Selection.Interior .ColorIndex = 11 End With ActiveChart.SeriesCollection(1).Points(8).Select With Selection.Border .Weight = xlThin .LineStyle = xlAutomatic End With With Selection.Interior .ColorIndex = 11 .Pattern = xlSolid End With ActiveChart.SeriesCollection(1).Points(10).Select With Selection.Border .Weight = xlThin .LineStyle = xlAutomatic End With With Selection.Interior .ColorIndex = 11 .Pattern = xlSolid End With ActiveChart.SeriesCollection(1).Points(12).Select With Selection.Border .Weight = xlThin .LineStyle = xlAutomatic End With With Selection.Interior .ColorIndex = 11 .Pattern = xlSolid End With ActiveChart.SeriesCollection(1).Points(14).Select With Selection.Border .Weight = xlThin .LineStyle = xlAutomatic End With Selection.Shadow = False Selection.InvertIfNegative = False With Selection.Interior .ColorIndex = 11 .Pattern = xlSolid End With End Sub Finally, there may be a way for the user to select the series; I'm thinking of something like the following (which I found on this DG). Dim myRange As Range Dim myAdd As String Set myRange = Range("A1") myRange.Offset(0, 3) = ActiveSheet.Range("A1") Set myRange = Application.InputBox( _ "Select Range to link from", Type:=8) However, this doesn't work for some reason. Any guidance would be much appreciated. Regards, Ryan--- -- RyGuy |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
One Range, Two Colors
Sub AlternateBarColors
Dim iPt As Long With ActiveChart.SeriesCollection(1) For iPt = 1 To .Points.Count Select Case iPt Mod 2 Case 0 .ColorIndex = 7 Case 1 .ColorIndex = 4 End Select Next End With End Sub - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "John Mansfield" wrote in message ... In answer to your first question, this macro will loop through a series (in this case series #1) and alternate the colors based on the colorindex numbers that you choose: Sub AlternateSeriesColors() Dim A As Integer Dim B As Integer A = 1 B = 2 Do On Error GoTo ErrHandler: ActiveChart.SeriesCollection(1).Points(A).Interior .ColorIndex = 4 ActiveChart.SeriesCollection(1).Points(B).Interior .ColorIndex = 7 A = A + 2 B = B + 2 Loop ErrHandler: Exit Sub End Sub ----------------- John Mansfield "ryguy7272" wrote: I am wondering if there is a way to get Excel to loop through a series of data, build a chart based on this series of data, and alternate the colors of the bars on the chart. I guess the even points have to be a variable (i.e., Points(2).Select, Points(4).Select, etc.) This kind of a pain to do if looking at only one series. This is the macro that I recorded: Sub Macro1() ActiveSheet.ChartObjects("Chart 1").Activate ActiveChart.SeriesCollection(1).Select With Selection.Border .Weight = xlThin .LineStyle = xlAutomatic End With With Selection.Interior .ColorIndex = 3 .Pattern = xlSolid End With ActiveChart.SeriesCollection(1).Points(2).Select With Selection.Border .Weight = xlThin .LineStyle = xlAutomatic End With With Selection.Interior .ColorIndex = 11 .Pattern = xlSolid End With ActiveChart.SeriesCollection(1).Points(4).Select With Selection.Border .Weight = xlThin .LineStyle = xlAutomatic End With Selection.Shadow = False With Selection.Interior .ColorIndex = 11 .Pattern = xlSolid End With ActiveChart.SeriesCollection(1).Points(6).Select With Selection.Border .Weight = xlThin .LineStyle = xlAutomatic End With With Selection.Interior .ColorIndex = 11 End With ActiveChart.SeriesCollection(1).Points(8).Select With Selection.Border .Weight = xlThin .LineStyle = xlAutomatic End With With Selection.Interior .ColorIndex = 11 .Pattern = xlSolid End With ActiveChart.SeriesCollection(1).Points(10).Select With Selection.Border .Weight = xlThin .LineStyle = xlAutomatic End With With Selection.Interior .ColorIndex = 11 .Pattern = xlSolid End With ActiveChart.SeriesCollection(1).Points(12).Select With Selection.Border .Weight = xlThin .LineStyle = xlAutomatic End With With Selection.Interior .ColorIndex = 11 .Pattern = xlSolid End With ActiveChart.SeriesCollection(1).Points(14).Select With Selection.Border .Weight = xlThin .LineStyle = xlAutomatic End With Selection.Shadow = False Selection.InvertIfNegative = False With Selection.Interior .ColorIndex = 11 .Pattern = xlSolid End With End Sub Finally, there may be a way for the user to select the series; I'm thinking of something like the following (which I found on this DG). Dim myRange As Range Dim myAdd As String Set myRange = Range("A1") myRange.Offset(0, 3) = ActiveSheet.Range("A1") Set myRange = Application.InputBox( _ "Select Range to link from", Type:=8) However, this doesn't work for some reason. Any guidance would be much appreciated. Regards, Ryan--- -- RyGuy |
#5
Posted to microsoft.public.excel.charting
|
|||
|
|||
One Range, Two Colors
Brilliant!!! Worked great John! The final version is listed below (maybe
others can find some value added in this Sub too). Jon, thanks for responding; yours is great too...I just ended up going with Johns recommendation. Sub UserInput() Dim rRange As range Dim ws As Worksheet Set rRange = Nothing On Error Resume Next Set rRange = Application.InputBox(Prompt:= _ "Please select a range for input.", _ Title:="SPECIFY RANGE", Type:=8) On Error GoTo 0 If rRange Is Nothing Then Exit Sub End If InValidEntry: If Err = 13 Then MsgBox "Not a valid input. " & "Please retry." End If rRange.Select Charts.Add ActiveChart.ChartType = xlColumnClustered ActiveChart.Location Whe=xlLocationAsObject, Name:="Chart" ActiveChart.Legend.Select Selection.Delete Dim A As Integer Dim B As Integer A = 1 B = 2 Do On Error GoTo ErrHandler: ActiveChart.SeriesCollection(1).Points(A).Interior .ColorIndex = 4 ActiveChart.SeriesCollection(1).Points(B).Interior .ColorIndex = 7 A = A + 2 B = B + 2 Loop ErrHandler: Exit Sub End Sub -- RyGuy "John Mansfield" wrote: In answer to your first question, this macro will loop through a series (in this case series #1) and alternate the colors based on the colorindex numbers that you choose: Sub AlternateSeriesColors() Dim A As Integer Dim B As Integer A = 1 B = 2 Do On Error GoTo ErrHandler: ActiveChart.SeriesCollection(1).Points(A).Interior .ColorIndex = 4 ActiveChart.SeriesCollection(1).Points(B).Interior .ColorIndex = 7 A = A + 2 B = B + 2 Loop ErrHandler: Exit Sub End Sub ----------------- John Mansfield "ryguy7272" wrote: I am wondering if there is a way to get Excel to loop through a series of data, build a chart based on this series of data, and alternate the colors of the bars on the chart. I guess the even points have to be a variable (i.e., Points(2).Select, Points(4).Select, etc.) This kind of a pain to do if looking at only one series. This is the macro that I recorded: Sub Macro1() ActiveSheet.ChartObjects("Chart 1").Activate ActiveChart.SeriesCollection(1).Select With Selection.Border .Weight = xlThin .LineStyle = xlAutomatic End With With Selection.Interior .ColorIndex = 3 .Pattern = xlSolid End With ActiveChart.SeriesCollection(1).Points(2).Select With Selection.Border .Weight = xlThin .LineStyle = xlAutomatic End With With Selection.Interior .ColorIndex = 11 .Pattern = xlSolid End With ActiveChart.SeriesCollection(1).Points(4).Select With Selection.Border .Weight = xlThin .LineStyle = xlAutomatic End With Selection.Shadow = False With Selection.Interior .ColorIndex = 11 .Pattern = xlSolid End With ActiveChart.SeriesCollection(1).Points(6).Select With Selection.Border .Weight = xlThin .LineStyle = xlAutomatic End With With Selection.Interior .ColorIndex = 11 End With ActiveChart.SeriesCollection(1).Points(8).Select With Selection.Border .Weight = xlThin .LineStyle = xlAutomatic End With With Selection.Interior .ColorIndex = 11 .Pattern = xlSolid End With ActiveChart.SeriesCollection(1).Points(10).Select With Selection.Border .Weight = xlThin .LineStyle = xlAutomatic End With With Selection.Interior .ColorIndex = 11 .Pattern = xlSolid End With ActiveChart.SeriesCollection(1).Points(12).Select With Selection.Border .Weight = xlThin .LineStyle = xlAutomatic End With With Selection.Interior .ColorIndex = 11 .Pattern = xlSolid End With ActiveChart.SeriesCollection(1).Points(14).Select With Selection.Border .Weight = xlThin .LineStyle = xlAutomatic End With Selection.Shadow = False Selection.InvertIfNegative = False With Selection.Interior .ColorIndex = 11 .Pattern = xlSolid End With End Sub Finally, there may be a way for the user to select the series; Im thinking of something like the following (which I found on this DG)€¦ Dim myRange As Range Dim myAdd As String Set myRange = Range("A1") myRange.Offset(0, 3) = ActiveSheet.Range("A1") Set myRange = Application.InputBox( _ "Select Range to link from", Type:=8) However, this doesnt work for some reason€¦ Any guidance would be much appreciated. Regards, Ryan--- -- RyGuy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Addin:Setting the range to the Excel.Range object range prop | Excel Worksheet Functions | |||
Create drop down box with the formatting of range cells colors ect | Excel Worksheet Functions | |||
Can't format font colors or cell fill-in colors | Excel Discussion (Misc queries) | |||
colors | Excel Worksheet Functions | |||
colors | Excel Worksheet Functions |