Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to catch the colorindex in a series
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to catch the colorindex in a 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to catch the colorindex in a 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to catch the colorindex in a 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to catch the colorindex in a 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to catch the colorindex in a 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to catch the colorindex in a 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to catch the colorindex in a series
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 "Peter T" wrote: 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to catch the colorindex in a 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to catch the colorindex in a series
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 "Vic Eldridge" wrote in message ... 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 "Peter T" wrote: 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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to catch the colorindex in a 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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to catch the colorindex in a series
typo -
needed to relate to the "same as" chlorine chlorine ColorIndex Peter T |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to catch the colorindex in a series
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" wrote: 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 "Vic Eldridge" wrote in message ... 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 "Peter T" wrote: 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 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to catch the colorindex in a series
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 "Vic Eldridge" wrote in message ... 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" wrote: 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 "Vic Eldridge" wrote in message ... 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 snip < |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
HOW TO CATCH A DIFFERENT/INCONSISTENT FORMULA | Excel Discussion (Misc queries) | |||
Color vs Colorindex for series border | Charts and Charting in Excel | |||
Trying to change ColorIndex for series settings | Charts and Charting in Excel | |||
how to catch errors from outlook | Excel Programming | |||
Catch-22 with Error 59 | Excel Programming |