Trendline color format bug/issue?

[satishbhave]

I want to change the trendlines color/type/width to match the data series
line color. i have this vb code which works well and changes type and width
but not color. what i found was that to make this work, first, i need to
manually change the color of trendlines and then run the macro.. i don;t know
if this is a bug or something i am not doing properly

Sub change_trendlines()
Dim mysrcolor, myset As ColorFormat
Dim ch1 As ChartObject
Dim series1 As Series
Dim trend1 As Trendline
Dim myline As LineFormat


For Each ch1 In ActiveSheet.ChartObjects
ch1.Activate
For Each series1 In ActiveChart.SeriesCollection
Set mysrcolor = series1.Format.Line.ForeColor
For Each trend1 In series1.Trendlines
Set myline = trend1.Format.Line
myline.DashStyle = msoLineDash
myline.Weight = 2
myline.ForeColor.RGB = mysrcolor.RGB
Next trend1
Next series1
ch1.Activate
Next ch1

End Sub

any help on this is highly appreciated

 

[Peter T]

There are various reasons why the following might not work correctly for you
but see how you get on.

Sub change_trendlines()
'Dim mysrcolor As ColorFormat, myset As ColorFormat
Dim ch1 As ChartObject
Dim series1 As Series
Dim trend1 As Trendline
'Dim myline As LineFormat
Dim n As Long

' assumes ALL Line type series in ALL charts

For Each ch1 In ActiveSheet.ChartObjects
n = 24
For Each series1 In ch1.Chart.SeriesCollection
If n = 56 Then n = 0
n = n + 1
cx = series1.Border.ColorIndex
If cx = xlAutomatic Then cx = n

For Each trend1 In series1.Trendlines

With trend1.Border
.LineStyle = msoLineDash
.Weight = 2
.ColorIndex = cx
End With

Next trend1
Next series1
Next ch1

End Sub

Regards,
Peter T

 

[satishbhave]

Thanks Peter, it does work for all trendlines but the color assigned to
trendline is not exactly same as it's data series color. the RGB was giving
me exactly same color as the parent data series line compared to COLORINDEX
color...any suggestions ?
-satish

 

[Peter T]

I don't understand what you are saying, it's contradictory -

"the color assigned to trendline is not exactly same as it's data series
color"
vs
"the RGB was giving me exactly same color as the parent data series line"

If you have standard line type charts with lines in default colours, the
macro I posted should format trendlines with same colour as the "automatic"
colour or the customized colour of the parent series line.

If your charts are not "typical" eg mixed with bar type and line type
series, or has been modified in certain other ways, the macro (as posted)
will give incorrect results

Regards,
Peter T

 

[satishbhave]

Here is a sample file with your code in it (and my code as _old). not sure
how to upload it here.....so you may have to create any sample line graph
with 2 data series and 1 trendlines for each data series and then try your
code as well as my code...

I just tested it with your code and it does change the color/width/type of
trendlines but I see a little difference in color of trendlines compared to
data series line. Is it due to I am using 32-bit high resolution on Vista ?

If you then change the colors of data series as well as trendlines manually
to any 4 different colors and then run my macro, the trendlines exactly
change to the colors of the data series lines…

really appreciate your help on this.
-satish

 

[Peter T]

Which version of Excel are you using. If it's 2007 the code I posted may not
work as expected.

Regards,
Peter T

 

[satishbhave]

yes i am on Excel 2007 and that could be the reason...
-satish

 

[Peter T]

Ah, 2007. Afraid I can't help you. If someone else doesn't come up with the
answer in a day or two it might be worth reposting. Try with a subject line
something like "How to format trendline same color as series in XL2007"

Or maybe you can work it out yourself. In earlier versions with Line type
series colours are applied "automatically", ie the border's colorindex is
xlAutomatic, and the actual RGB color cannot be returned. These automatic
colours are taken from the chart colours starting in the bottom row of the
56-colour palette starting at colorindex 25. In 2007 there is probably some
similar logic as to how the "automatic" colours are applied.

Regards,
Peter T

 

[Phil Outram.]

Hi,

I also have this issue of needing to find the colour of automatic traces
(including instances where there are markers and not lines - i.e. you can't
use the border property) and figure that there must some internal index
associated with each trace as it's created that could be used to determine
which automatic colorindex it uses.

The reason I say this is that if you add 3 traces and then delete the first
one, the second and third still use the second and third automatic colours.
I think the above example code would set them to the first and second
automatic colours, not realising that the first automatic trace had been
deleted. Therefore Excel must know and store an index number for the trace.
Additionally, if you then add another trace, it uses the first automatic
colours (it knows that this one has been deleted). So Excel knows more than
it's letting on and if anyone has any idea of how I can find this additional
information I'd be hugely grateful.

Many thanks,

Phil.

 

[Phil Outram.]

Struggling to find an answer to this, I emailed Jon Peltier, a Microsoft
Excel MVP, and he replied with this very useful answer - thanks Jon...

Hi Phil -

Just so happens I looked this up the other day, so it's fresh in my mind.

VBA has no knowledge of this hidden information, but you can use the
antiquated XLM programming language to find it.

Every series is given an index. When you create three series, they are
numbered 1, 2, 3, and the default formatting is represented by these index
numbers. If you delete the first series, the remaining two are still numbered
2,3 (the numbers never change for a series), and their default formatting is
still based on these. Say you add two more series. The first takes over the
index 1, which was vacant, and its default formatting, while the second takes
the next vacant number, 4.

The first automatic color for a chart series fill is 17, and the first
automatic line or border color is 25.

You can read these numbers using XLM as follows. Select an object, and run
this:

sXLMCode = ExecuteExcel4Macro("selection()")

If you've selected a series, sXLMCode is of the form "Si", where i is the
magic series index. If you've selected a point, sXLMCode is of the form
"SiPj", where i is the series index, and j is the point index.

I should pretty this explanation up and post it in my blog.

- Jon

-------

Jon Peltier, Microsoft Excel MVP

Tutorials and Custom Solutions

Peltier Technical Services, Inc. - http://PeltierTech.com _______