Home Menu

Menu


ExcelBanter » Excel Newsgroups » Charts and Charting in Excel > One Range, Two Colors
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
  
Posts: 2,836
Default 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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
  
Posts: 235
Default 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
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
  
Posts: 6,582
Default 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


Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
  
Posts: 6,582
Default 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


Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
  
Posts: 2,836
Default 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 With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel Addin:Setting the range to the Excel.Range object range prop Rp007 Excel Worksheet Functions 5 November 24th 06 04:30 PM
Create drop down box with the formatting of range cells colors ect Tom Excel Worksheet Functions 0 January 10th 06 09:17 PM
Can't format font colors or cell fill-in colors canoeron Excel Discussion (Misc queries) 3 August 22nd 05 11:46 PM
colors Mir Khan Excel Worksheet Functions 3 August 17th 05 03:40 AM
colors Gabbi Excel Worksheet Functions 4 July 27th 05 08:59 PM


All times are GMT +1. The time now is 03:42 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement