Custom Number format in charts vs. cells

[TrippyTom]

Hi gang,

Unfortunately we're switching to Office 2007 at work and in testing I ran into this strange "bug?" that Microsoft doesn't seem to care about because it's behaving the same way in Office 2010 as well.

Here's the situation. If you open the attached file, I have the same custom number format in the chart's data labels and in the cells. Why does it work in the cells but not when used on data labels in a chart?

According to this site it should be [color25] that I'm going after (a custom red).
click here

I'm so confused.

[TrippyTom]

forgot to attach my file sample...

[Aflatoon]

Curiously, as far as I can tell, every other number code seems to work the same in a cell as in the datalabels except for [color25].

[Bob Phillips]

You have chosen one of the chart CIs, and guess what, 25 is dark blue, which is what you get. Use numbers between 1-16 and 33-56.

[Paul_Hossler]

I'm not sure that you can do that -- from Help ...

Specify colors To specify the color for a section of the format, type the name of one of the following eight colors enclosed in square brackets in the section. The color code must be the first item in the section. [Black][Green][White][Blue][Magenta][Yellow][Cyan][Red]

[red] works



Paul

[Bob Phillips]

You can Paul, it is an (AFAIK) an undocumented feature.

[TrippyTom]

XLD, I changed one of the other colors in the range you specified and it's still behaving the same way. It doesn't seem to be honoring the same set of colors at all, because I changed color38 to the my custom color and it still chose the color from the original set instead of my custom color.

If you go into Tools > Options > SAVE settings > then click on the COLORS button it will show you the color window that the [color] feature follows in formulas, but it doesn't seem to be the same for charts.

Note: This works as expected in Excel2003, but not in 2007 or 2010.

[HannaPearson]

Ok. Thanks for these instructions. It is really helpful. My VBA is now working.