Get the color of the Colorscale

[Tibo]

Hi!

I have a list of numbers, and I applied a conditionnal formatting, a colorscale.

But I want to use the color given by the conditionnal formatting to color forms. So first I want to have the color number, or another list of cells colored with "Interior.color" and not with the coniditionnal formatting.
I've seen some functions, made by byunt "get cell color" (I can't insert links, but it's somewhere in this website!)
But it's not working in excel 2007 with a colorscale.

Does anybody has an idea ?

Thanks in advance.

Thibaut

[Bob Phillips]

I think you will have to determine the value and work from that, forget the colours, they are just a presentation layer (even more so in 2007).

[Tibo]

Do you mean I will have to code a macro or a simple function that give the color number in function of the figures ? I thought I could do that (plain red is the bigger, and green the smaller), but I could'nt find a proper way to have a nice colorscale with the color code (in RGB)...

[Bob Phillips]

I think we are talking about the same thing, but not absolutely sure what you want to do.

The problem is that if you look at the CF, with numbers 1 to 10, 10 is the far end red, but if you have numbers 1 to 40, 40 is red. What value do you want to assign as red in your manipulation of the data? To further complicate it, the colours in between the bounds will depend upon the numb er of values in the set being formated.

Personally, I find all of this fancy formatting in 2007 a total annoyance.It adds nothing to the quality of the preentation that I can see, and it is a nightmare to work with.

[Tibo]

OK.
I think we're not speaking of the same thing.
I want a colorscale, that is to say, for a serie, I want the biggest to be far end red, and the smallest far end blue for instance.
I don't care the number of colours, I don't care what colour represents what in absolute. It is to work in a relative way.
In my example sheet, the numbers were 1 to 10 for example, but I can have a totally different serie, but i still want the same colorscale, i.e. the same colors applied to the limits.

[Bob Phillips]

Then I have absolutely no idea what you are trying to do. It seems to me that you are saying '... I want to do just what I am doing ..' so I don't see your problem.

[phendrena]

Then I have absolutely no idea what you are trying to do. It seems to me that you are saying '... I want to do just what I am doing ..' so I don't see your problem.

*I think* what he is talking about is a colour gradiant scale.

Red at one end and goes to blue at the other end (A hot to cold fade for example). So regardless of how many numbers are in his range it should fade between the colours.

[Bob Phillips]

But that is exactly what he already has.

[Tibo]

It's exaclty what I have, except that I can't get the code for the color to re-use it for coloring a form, for example, because I can't get the information of the conditioning formatting.