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

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" wrote in message
...

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

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:
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" wrote in message
...

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

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


"leglouton" wrote:


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:
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" wrote in message
...

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

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

"Vic Eldridge" wrote in message
...
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


"leglouton" wrote:


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:
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" wrote in message
...

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

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

"Peter T" <peter_t@discussions wrote in message
...
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

"Vic Eldridge" wrote in message
...
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


"leglouton" wrote:


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:
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" wrote in message
...

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

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


"leglouton" wrote in message
...

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:
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" wrote in message
...

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

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 :
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


"leglouton" wrote in message
...

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:

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" wrote in message
...


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

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


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 :
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


"leglouton" wrote in message
...

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:

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" wrote in message
...


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