Problem with Setting Bar Chart Colors ( 56 bars)
 

Hi:

Excel 2003, Windows XP Professional

I have set up several bar charts and have a macro to set all the bar
colors to one color except for ones that are special.

The charts have about 80 - 100 bars or so.

Beyond the 56th. bar the color won't change. No matter what I do it
always reverts to the color Excel had set automatically.

It does work if I go in and change it manually but not via the macro.
I have many charts to do and do not wan't to do this manualy. Help!

The macro works fine on the bars 56 and below. As it goes above 56,
the color change doen't happen. Problem seems to have something to do
with color palette. Code looks like this:

For Each sr In chts.SeriesCollection
sr.Interior.ColorIndex = 23
Next sr

Many thanks for the help.

Problem with Setting Bar Chart Colors ( 56 bars)
 

Interesting problem.

If you are going to color all the bars the same color, why not just have all
your datapoints in one series.

--
Regards,
Tom Ogilvy


"ER" wrote:

Hi:

Excel 2003, Windows XP Professional

I have set up several bar charts and have a macro to set all the bar
colors to one color except for ones that are special.

The charts have about 80 - 100 bars or so.

Beyond the 56th. bar the color won't change. No matter what I do it
always reverts to the color Excel had set automatically.

It does work if I go in and change it manually but not via the macro.
I have many charts to do and do not wan't to do this manualy. Help!

The macro works fine on the bars 56 and below. As it goes above 56,
the color change doen't happen. Problem seems to have something to do
with color palette. Code looks like this:

For Each sr In chts.SeriesCollection
sr.Interior.ColorIndex = 23
Next sr

Many thanks for the help.

Problem with Setting Bar Chart Colors ( 56 bars)
 

Try this -

For Each sr In chts.SeriesCollection
with sr.Interior
.pattern = 1
.ColorIndex = 23
end with
Next sr

Regards,

Peter T


"ER" wrote in message
ps.com...
Hi:

Excel 2003, Windows XP Professional

I have set up several bar charts and have a macro to set all the bar
colors to one color except for ones that are special.

The charts have about 80 - 100 bars or so.

Beyond the 56th. bar the color won't change. No matter what I do it
always reverts to the color Excel had set automatically.

It does work if I go in and change it manually but not via the macro.
I have many charts to do and do not wan't to do this manualy. Help!

The macro works fine on the bars 56 and below. As it goes above 56,
the color change doen't happen. Problem seems to have something to do
with color palette. Code looks like this:

For Each sr In chts.SeriesCollection
sr.Interior.ColorIndex = 23
Next sr

Many thanks for the help.

Problem with Setting Bar Chart Colors ( 56 bars)
 

I needed to have all the data in different series so that I could do
various things conditional on the serries name.

I fixed the problem by first setting the ColorIndex for each to xlNone
and then going back and setting them all to blue except for ones I
wanted to highlight in yellow.

Interesting how the xlNone thing worked eh?

Many thanks for the help.

Tom Ogilvy wrote:
Interesting problem.

If you are going to color all the bars the same color, why not just have all
your datapoints in one series.

--
Regards,
Tom Ogilvy


"ER" wrote:

Hi:

Excel 2003, Windows XP Professional

I have set up several bar charts and have a macro to set all the bar
colors to one color except for ones that are special.

The charts have about 80 - 100 bars or so.

Beyond the 56th. bar the color won't change. No matter what I do it
always reverts to the color Excel had set automatically.

It does work if I go in and change it manually but not via the macro.
I have many charts to do and do not wan't to do this manualy. Help!

The macro works fine on the bars 56 and below. As it goes above 56,
the color change doen't happen. Problem seems to have something to do
with color palette. Code looks like this:

For Each sr In chts.SeriesCollection
sr.Interior.ColorIndex = 23
Next sr

Many thanks for the help.

Problem with Setting Bar Chart Colors ( 56 bars)
 

Nice,
That worked for me in xl2003 as well. Setting the pattern before setting
the colorindex appeared to be essential as well.

--
Regards,
Tom Ogilvy



"Peter T" wrote:

Try this -

For Each sr In chts.SeriesCollection
with sr.Interior
.pattern = 1
.ColorIndex = 23
end with
Next sr

Regards,

Peter T


"ER" wrote in message
ps.com...
Hi:

Excel 2003, Windows XP Professional

I have set up several bar charts and have a macro to set all the bar
colors to one color except for ones that are special.

The charts have about 80 - 100 bars or so.

Beyond the 56th. bar the color won't change. No matter what I do it
always reverts to the color Excel had set automatically.

It does work if I go in and change it manually but not via the macro.
I have many charts to do and do not wan't to do this manualy. Help!

The macro works fine on the bars 56 and below. As it goes above 56,
the color change doen't happen. Problem seems to have something to do
with color palette. Code looks like this:

For Each sr In chts.SeriesCollection
sr.Interior.ColorIndex = 23
Next sr

Many thanks for the help.

Problem with Setting Bar Chart Colors ( 56 bars)
 

Explanation -

The 'automatic' Pattern in series is Solid for the first 56, 50% grey in
57-112, then 70% grey, and new pattern every 56 thereafter.

If you apply a non automatic series colour the automatic pattern changes to
its 'real' pattern, ie 50$ grey in series 57+ and hence the OP's apparent
problem.

If you apply colorindex = xlNone (as noted by the OP in his reply to you)
that changes the automatic pattern (50% grey) to no pattern. When you apply
a new colorindex the original automatic Pattern is lost and is applied as 1
(solid).

Although first applying colorindex xlNone works, simpler and faster just to
apply Pattern = 1 (xlSolid)

Regards,
Peter T

"Tom Ogilvy" wrote in message
...
Nice,
That worked for me in xl2003 as well. Setting the pattern before setting
the colorindex appeared to be essential as well.

--
Regards,
Tom Ogilvy



"Peter T" wrote:

Try this -

For Each sr In chts.SeriesCollection
with sr.Interior
.pattern = 1
.ColorIndex = 23
end with
Next sr

Regards,

Peter T


"ER" wrote in message
ps.com...
Hi:

Excel 2003, Windows XP Professional

I have set up several bar charts and have a macro to set all the bar
colors to one color except for ones that are special.

The charts have about 80 - 100 bars or so.

Beyond the 56th. bar the color won't change. No matter what I do it
always reverts to the color Excel had set automatically.

It does work if I go in and change it manually but not via the macro.
I have many charts to do and do not wan't to do this manualy. Help!

The macro works fine on the bars 56 and below. As it goes above 56,
the color change doen't happen. Problem seems to have something to do
with color palette. Code looks like this:

For Each sr In chts.SeriesCollection
sr.Interior.ColorIndex = 23
Next sr

Many thanks for the help.

Problem with Setting Bar Chart Colors ( 56 bars)
 

Poorly written -

If you apply a non automatic series colour the automatic pattern changes
to
its 'real' pattern, ie 50$ grey in series 57+ and hence the OP's apparent
problem.

After applying a new interior colour the pattern DOESN'T change but it no
longer returns -4105 (xlAutomtic), it will now return its 'real' xlGray50
pattern in series 57+.

Regards,
Peter T

Typo -
"Peter T" <peter_t@discussions wrote in message
...
Explanation -

The 'automatic' Pattern in series is Solid for the first 56, 50% grey in
57-112, then 70% grey, and new pattern every 56 thereafter.

If you apply a non automatic series colour the automatic pattern changes
to
its 'real' pattern, ie 50$ grey in series 57+ and hence the OP's apparent
problem.

If you apply colorindex = xlNone (as noted by the OP in his reply to you)
that changes the automatic pattern (50% grey) to no pattern. When you
apply
a new colorindex the original automatic Pattern is lost and is applied as
1
(solid).

Although first applying colorindex xlNone works, simpler and faster just
to
apply Pattern = 1 (xlSolid)

Regards,
Peter T

<snip

Problem with Setting Bar Chart Colors ( 56 bars)
 

I used his code to set each bar to colorindex 24

When I queried those series, they all had a colorindex of 23 even though the
bars above 56 did not change their appearance. (the colors were all
different (just as they originally were) rather than a colorindex of 23 with
some % gray.

Using your explanation, shouldn't they all have been some shade of patterned
blue - if pattern were the sole cause.

--
Regards,
Tom Ogilvy

"Peter T" <peter_t@discussions wrote in message
...
Poorly written -

If you apply a non automatic series colour the automatic pattern changes
to
its 'real' pattern, ie 50$ grey in series 57+ and hence the OP's apparent
problem.

After applying a new interior colour the pattern DOESN'T change but it no
longer returns -4105 (xlAutomtic), it will now return its 'real' xlGray50
pattern in series 57+.

Regards,
Peter T

Typo -
"Peter T" <peter_t@discussions wrote in message
...
Explanation -

The 'automatic' Pattern in series is Solid for the first 56, 50% grey in
57-112, then 70% grey, and new pattern every 56 thereafter.

If you apply a non automatic series colour the automatic pattern changes
to
its 'real' pattern, ie 50$ grey in series 57+ and hence the OP's apparent
problem.

If you apply colorindex = xlNone (as noted by the OP in his reply to you)
that changes the automatic pattern (50% grey) to no pattern. When you
apply
a new colorindex the original automatic Pattern is lost and is applied as
1
(solid).

Although first applying colorindex xlNone works, simpler and faster just
to
apply Pattern = 1 (xlSolid)

Regards,
Peter T

<snip

Problem with Setting Bar Chart Colors ( 56 bars)
 

I think applying the OP's original code does this -

Series 1-56
Colorindex #23, Pattern solid, Patterncolorindex #2
as pattern is solid the white pattern colour not shown so colours are as
expected.

Series 57+
Colorindex #23, Pattern gray50%, Patterncolorindex #17+
so colours in series 57+ change to 50/50 mix of #23 & #17+

If Pattern is also changed to Solid the pattern colours in 57+ will be as
expected. The Patterncolorindex also changes as above though now not
visible.

I assume the logic is to avoid duplicate fill colours in large multi series
charts.


Following routines to demonstrate the underlying automatic interior
formats -

Make2Charts: a pair of identical 60 series charts

Run ToggleFormats on chart-2 with bDefault = False and compare with chart-1,
they should still appear same.

Sub Make2Charts()
Dim cht As Chart, sr As Series
Dim i As Long, t As Long

ActiveSheet.ChartObjects.Delete ' ###
Application.ScreenUpdating = False

For t = 15 To 165 Step 150
Set cht = ActiveSheet.ChartObjects.Add(10, t, 1200, 120).Chart
cht.ChartType = xlColumnClustered
cht.HasLegend = False

For i = 1 To 60
Set sr = cht.SeriesCollection.NewSeries
sr.Values = Array(100 + i)
sr.HasDataLabels = True
sr.Points(1).DataLabel.Text = i
Next
cht.PlotArea.Top = 15
cht.PlotArea.Height = 105
Next

Application.ScreenUpdating = True
Range("S1").Select
errH:

'if charts not visible select them with cursor over rows 5 & 15

End Sub

Sub ToggleFormats()
Dim cht As Chart, sr As Series
Dim i As Long
Dim bDefault As Boolean

'chart-2: toggle automatic formats < identical custom formats

'Pattern: 1-56 solid, 57-112 gray50%, 113-168 gray70%
'PatternColorIndex: maps to lightest colour, #2 in default palette
'Colorindex: loops #1 to #56 starting with #17 in series 1 (in a bar type)

Set cht = ActiveSheet.ChartObjects(2).Chart

bDefault = False ' false-custom, true-automatic

For i = 1 To cht.SeriesCollection.Count
With cht.SeriesCollection(i).Interior

If Not bDefault Then
.Pattern = IIf(i 56, xlGray50, xlSolid)
.PatternColorIndex = 2
.Colorindex = (i + 15) Mod 56 + 1
Else
.Pattern = xlAutomatic
.PatternColorIndex = xlAutomatic
.Colorindex = xlAutomatic
End If

Debug.Print i, .Pattern, .PatternColorIndex, .Colorindex
End With
Next

End Sub

Re my comment about automatic PatternColorIndex maps to lightest colour in a
customized palette -

This doesn't work the same way as mapping a cell rgb colour to nearest
palette colour, also inconsistent depending on a range of scenarios. #2 can
sometimes be applied even if #2 is not the lightest colour.

Regards,
Peter T


"Tom Ogilvy" wrote in message
...
I used his code to set each bar to colorindex 24

When I queried those series, they all had a colorindex of 23 even though
the
bars above 56 did not change their appearance. (the colors were all
different (just as they originally were) rather than a colorindex of 23
with
some % gray.

Using your explanation, shouldn't they all have been some shade of
patterned
blue - if pattern were the sole cause.

--
Regards,
Tom Ogilvy

"Peter T" <peter_t@discussions wrote in message
...
Poorly written -

If you apply a non automatic series colour the automatic pattern
changes
to
its 'real' pattern, ie 50$ grey in series 57+ and hence the OP's
apparent
problem.

After applying a new interior colour the pattern DOESN'T change but it
no
longer returns -4105 (xlAutomtic), it will now return its 'real'
xlGray50
pattern in series 57+.

Regards,
Peter T

Typo -
"Peter T" <peter_t@discussions wrote in message
...
Explanation -

The 'automatic' Pattern in series is Solid for the first 56, 50% grey
in
57-112, then 70% grey, and new pattern every 56 thereafter.

If you apply a non automatic series colour the automatic pattern
changes
to
its 'real' pattern, ie 50$ grey in series 57+ and hence the OP's
apparent
problem.

If you apply colorindex = xlNone (as noted by the OP in his reply to
you)
that changes the automatic pattern (50% grey) to no pattern. When you
apply
a new colorindex the original automatic Pattern is lost and is applied
as
1
(solid).

Although first applying colorindex xlNone works, simpler and faster
just
to
apply Pattern = 1 (xlSolid)

Regards,
Peter T

<snip