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