colorIndex listing for Chart with autoformat settings

  • Thread starter Thread starter Jean Ruch
  • Start date Start date
J

Jean Ruch

Hi,

Is there an access through VBA to the listing of the Border.ColorIndex
sequence used by Excel for SeriesCollection(n) in
dependence of "n"
in Charts based on default / autoformat settings ?

All my trials to acceed to this parameter for individual curves lead to
" - 4105" :-((

Thanks in advance
Regards

Jean
 
-4105 means default, as you must have guessed.

Getting the color of default-formatted series is not straightforward.
You can start by figuring the color index of the bottom two rows of the
palette, which Excel uses as default chart colors. They start at 25 (the
dark blue in the bottom left) and increase by one.

However, depending on the history of the chart, the colors follow
different sequences. Suppose I have a chart with series A, B, and C.
These have default colors 25, 26, and 27. Then I delete series B. A and
C keep the formatting they started with. Then I add series D, which is
plotted third in the plot order, but it takes on the formatting of the
departed series B (so they go 25-27-26 in sequence). Further, if I query
the series using the old fashioned XLM technique:

?ExecuteExcel4Macro("selection()")

Excel tells me that series D is also series 2. So the series default
format is tied to the series number as interpreted by XLM, which is
related to the order in which the series are added to the chart, keeping
in mind that any gaps in the sequence are filled by the next added
series. This is different than the series number as interpreted by VBA
(which is closely related to plot order).

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
Jon Peltier said:
-4105 means default, as you must have guessed.

Getting the color of default-formatted series is not straightforward.
You can start by figuring the color index of the bottom two rows of the
palette, which Excel uses as default chart colors. They start at 25 (the
dark blue in the bottom left) and increase by one.

However, depending on the history of the chart, the colors follow
different sequences. Suppose I have a chart with series A, B, and C.
These have default colors 25, 26, and 27. Then I delete series B. A and
C keep the formatting they started with. Then I add series D, which is
plotted third in the plot order, but it takes on the formatting of the
departed series B (so they go 25-27-26 in sequence). Further, if I query
the series using the old fashioned XLM technique:

?ExecuteExcel4Macro("selection()")

Excel tells me that series D is also series 2. So the series default
format is tied to the series number as interpreted by XLM, which is
related to the order in which the series are added to the chart, keeping
in mind that any gaps in the sequence are filled by the next added
series. This is different than the series number as interpreted by VBA
(which is closely related to plot order).

- Jon
-------

many thanks, Jon

Your input is - as usual - exactly what I needed for my purpose :
My Sub makes a listing - in the sheet - of the equation of the
trendlines of
complete arrays of curves showed in an embedded Chart .
In order to be able to assign at a glance the equation to it's
respective curve,
my aim was to color the font of the equation in the color of this
latter (on a greyed background of course....).
This works in my sub as intended only in the case where the color
of the curves has been assigned. (in the worst case manually .....)

As the operation takes place in a closed loop without any possibility
of manual intervention and as long as the Diagramm should not be
different
of what is get usually, all will be easy with your information.

Thanks again

Jean
 
Back
Top