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.

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.

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.

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.

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.

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.

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

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