Color cells by value/formula for continuously-variable color

[guyg]

Is there a way to colour a cell with a calculated colour?

Conditional formatting is limited to 3 styles. I'd like to colour cells according to value, but with a continuous range of colour, eg. white to red. style() and a lookup table might work, but it's a lot of work! There don't seem to be any functions to convert a number to a colour; ideally I'd like a cell formula something like '=style(color(A1*255,0,0))' to colour a shade of red

Anyone got any clever suggestions?
Thanks

[acknak]

Calc requires a specific style for every distinct cell format. It has no way to "change the color" of a cell, only "apply this style to this cell".

That means, no matter what, you have to have a style for every color you want to use, and there is no such thing (for Calc) as a "calculated" or "continuous" color. You have to pre-define all the colors (in separate style) you're going to use.

You might want to look at this enhancement request, it has some discussion and links that may give you some ideas: Issue 71392: Provide unlimited conditional formatting styles.

[Villeroy]

Create the required styles, put the style names into the second column of a helper table and the limiting values into the first column. Sort by values.

Code: Select all

=your_numeric_reference_or_formula+STYLE(VLOOKUP(CURRENT();style_map;2))