Conditional number format of x-axis based on value?

[hdf]

With Excel 2007, I was wondering if it is possible to conditionally
format the x-axis number format in the chart based on multiple
criteria.

I know I can do it for a value >x or < the y. But I need to do it for
ranges of values.

Essentially I want to have any value <1 and > -1 formatted as a %
value,
any value >1 and <500 or <-1 and >-500 formatted as a number with two
decimal places,
and any number >500 or <-500 formatted as a number with no decimal
places.

Using conditional formatting I have done this with the values that are
going to be used by the chart as the x-axis values. However, the
graph does not pick up the formatting created by Conditional
Formatting. The chart insists on using the underlying format of the
values before conditional formatting is applied.

Is this possible without using VBA? If not, what would be the VBA
code to get me there?

Thanks for any help out there.

 

[Ron Rosenfeld]

With Excel 2007, I was wondering if it is possible to conditionally
format the x-axis number format in the chart based on multiple
criteria.

I know I can do it for a value >x or < the y. But I need to do it for
ranges of values.

Essentially I want to have any value <1 and > -1 formatted as a %
value,
any value >1 and <500 or <-1 and >-500 formatted as a number with two
decimal places,
and any number >500 or <-500 formatted as a number with no decimal
places.

Using conditional formatting I have done this with the values that are
going to be used by the chart as the x-axis values. However, the
graph does not pick up the formatting created by Conditional
Formatting. The chart insists on using the underlying format of the
values before conditional formatting is applied.

Is this possible without using VBA? If not, what would be the VBA
code to get me there?

Thanks for any help out there.

I will watch this thread with interest. And you might also consider posting in the Answers forums at microsoft.com.

I don't think it is possible. When you link the axis label format to the source data, it seems to only use the format of the first cell. And if you specify conditions within a number format, you are limited to only three conditions. And the tick-mark labels of a chart axis get returned as a unit. There does not seem to be any object that represents a tick-mark label.

 

[hdf]

Ron,

Thanks for the suggestion about Microsoft Answer. I have now posted
the same question there too.