How to catch the colorindex in a series

[leglouton]

How to catch the line or the point's color in an object serie from a
chart when property is set to xlColorIndexAutomatic.

For example if My_series is a series object with his property
MarkerBackgroundColorIndex set to xlColorIndexAutomatic. How to catch
the color give by Excel to fill the points of my series

 

[Peter T]

If all you want to do is colour your point to the same colour as its marker,
apply the same colorindex as returned from the marker, even if xlautomatic.

You may want to ensure your point has a marker to avoid applying xlNone,
effectively invisible, eg

x = .MarkerBackgroundColorIndex
if x = xlNone then x = xlAutomatic
myPoint.border.colorindex = x.

If you have only one series and vary colors by point, you would need to do
each point individually.

Come back if you need the actual automatic colour for some other reason not
mentioned in your post.

Regards,
Peter T

 

[leglouton]

Thanks for your feedback but I need of the actual colour. I want to add
to my series a trendline with the same color. If I apply the same
colorindex with xlautomatic, the colour is different. The property
colorindex from an object trendline set to xlautomatic give always the
same color (black in my case) for each series.

leglouton

Peter T a écrit:

 

[Guest]

With ActiveChart.SeriesCollection(1)
.Trendlines(1).Border.ColorIndex = .Fill.ForeColor.SchemeColor
End With

It's a pity they didn't stick to a single convention.


Regards,
Vic Eldridge

 

[Peter T]

Hi leglouton,

You can use the fact that automatic colours for Series are always applied
"same as" colorindex 25 & on (Line types) and 17 & on (fill types), in the
order Series were added to the chart.

Eg the "same as" colorindex for the third series = 24 + 3

But, this nice workaround falls apart in any of the following scenarios, and
I think some others which elude me for the moment:

- Series are deleted, perhaps others added
- a non standard or combination type chart
- The original series order is changed (Format data series > Series order)

If you are in control of you chart and know the none of the above have not
occurred - no problem.

Otherwise you can get the Series identifier (not necessarily same as series
index) like this:

Sub TrendColourLikeSeries()
'pmbthornton gmail com
Dim n, e
Dim x As Long
Dim srID As String
Dim sMsg As String
Dim cht As Chart
Dim sr As Series
Dim tr As Trendline
Dim bLineType As Boolean

On Error GoTo errH

Set cht = ActiveChart
e = 0
For Each sr In cht.SeriesCollection
e = 10
bLineType = sr.MarkerStyle
e = 20

sr.Select
srID = ExecuteExcel4Macro("SELECTION()")
n = Val(Mid(srID, 2, 255))

If bLineType Then
x = sr.Border.ColorIndex
If x = xlNone Then
x = sr.MarkerBackgroundColorIndex
End If
Else
x = sr.Interior.ColorIndex
End If

If x < 1 Then
x = IIf(bLineType, 24, 16) + n
x = x Mod 56
End If

For Each tr In sr.Trendlines
tr.Border.ColorIndex = x
Next
Next

Exit Sub
errH:
Select Case e
Case 0: sMsg = "Chart not selected"
Case 10:
bLineType = False
Resume Next
Case Else: sMsg = "Error"
End Select
MsgBox e & vbCr & sMsg
End Sub

The above is overkill for purely Fill type series, with these get the
interior Fill colorindex. If xlAutomatic then get simply the .Color and
apply as the "long" colour value. Excel will colour match and apply a "same
as" colorindex (possibly in the upper part of the palette as many chart
colours are duplicated).

Regards,
Peter T

 

[Peter T]

Hi Vic,

This is neat if series are Fill types but from the OP's original post he
appears to be working with Line types, which would not include a Fill
property

Regards,
Peter T

 

[Peter T]

I wasn't concentrating - Line Types do have a Fill property but both
..ForeColor & .Backcolor.Schemecolor return 70, and RGB colour 0, whatever
the colour or colorindex of the Line.

Regards,
Peter T

 

[Guest]

Hi Peter,

You're right. What I posted does not meet the OPs requirements.
I believe the following does though.

With ActiveChart.SeriesCollection(1)
.Trendlines(1).Border.Color = .Border.Color
End With


Regards,
Vic Eldridge

 

[leglouton]

Hi Peter

I do not understand the fllowing line in your script:
srID = ExecuteExcel4Macro("SELECTION()")

But your script works very well.
Many Thanks

leglouton


Peter T a écrit :

 

[Peter T]

Hi Vic,

Your suggestion certainly works if user has applied a palette colour, though
could just return the colorindex and use that if 1-56.

Unfortunately, and unless there's a version difference I'm unaware of,
returning border colour of a Line type series with xlAutomatic colour will
return system black, typically 0 pure black or whatever is set in user's
system for Window text.

This is despite the fact the actual colour will be "same as" say #25 if
first series.

I don't know any alternative other than along the lines of the convoluted
approach I suggested to the OP, or maybe GetPixelColor perhaps.

Regards,
Peter T

 

[Peter T]

Hi leglouton,

Glad it works.

I do not understand the fllowing line in your script:
srID = ExecuteExcel4Macro("SELECTION()")

This is an old XLM that returns info as a string about a selected object. If
you include a line
Debug.Print srID
just below you will see what is returned. Something like "S1", parse out the
S to get the series identifier.

I don't know of a VBA alternative to get the identifier, needed to relate to
the "same as" chlorine. Typically this is same as the series index or number
but not necessarily for the reasons I mentioned.

This XLM works with the selection, hence the preceding .Select lines which
are not normally required in VBA.

Regards,
Peter T

 

[Peter T]

typo -

needed to relate to the "same as" chlorine

chlorine >> ColorIndex

Peter T

 

[Guest]

Hi Peter,

I think we are seeing a version difference. I'm running Excel 2003 on
Windows XP, and the following routine paints each trendline the same color as
it's series. As the code shows, the series' are indeed colored using
xlColorIndexAutomatic.

Dim srs As Series
For Each srs In ActiveChart.SeriesCollection
With srs
.Border.ColorIndex = xlColorIndexAutomatic
.Trendlines(1).Border.Color = .Border.Color
End With
Next srs

Interestingly enough, if I try to read the color from the
..MarkerBackgroundColor or the .MarkerForegroundColor, I get the same
erroneous results as what you're describing.

The convoluted approach works fine on your, the OP's and my system, so I
guess we can conclude that it's the most reliable solution - until MS finally
decide to put XLM out to pasture.


Regards,
Vic Eldridge

 

[Peter T]

Hi Vic,

Thanks for following up with that, useful info for my purposes. I'm
currently using XL2K.

Strange the anomaly is fixed in XL2003 for Line border xlAutomatic, yet not
for markers. The routine I posted catered for getting a colour for an
invisible (xlNone) Line with markers. I guess for such a Line the convoluted
approach would still be required even in XL2003.

Regards,
Peter