References in color-scale conditional formatting formulae for ranges

Question

I'm using Libreoffice Calc (v6.1b).

Suppose I have numeric data in sheet column, A and I'm applying conditional formatting to sheet column B. I want each cell of B to have a color on a scale depending on its corresponding A value. For example:

Cell B1 is Green when A1 is 0 turns Yellow as A1 approaches 1, turns Red when A1 approaches 2.
Cell B2 is Green when A2 is 0 turns Yellow as A2 approaches 1, turns Red when A2 approaches 2.

and so on. How do I make these relative references when entering a formula in Libreoffice's conditional formatting dialog?

Answer 1

Select cells B1 to B3. This way, B1 is the offset starting point. So, in conditional formatting formulas, A1 will indicate the cell to the left of the current cell, not necessarily the first row.

Now, under Format -> Conditional Formatting -> Manage, enter Formula is A1=0 and Apply Style "Good" (that is, green).

Then add A1=1 for "Neutral" (yellow) and A1=2 for "Bad" (red). Finally, hit OK.

EDIT:

Color scales currently require All Cells, and cannot be used with Formula is. So that leaves the following less-than-ideal solutions.

1. Write a macro to read the values and set the cell colors. Re-run it whenever the values change, by a sheet event handler.
2. Create for example 10 different styles of slightly different color. Then use STYLE and VLOOKUP to choose which of these styles should be used in each row.
3. Create those 10 different styles but use conditional formatting with Formula is to choose which style.

Methods 1 and 2 are explained in detail along with examples at https://forum.openoffice.org/en/forum/viewtopic.php?t=55410.

One good approach might be to use method 2 above but write a macro to create 100 different styles. Then the macro only needs to be run once to set up, and from then on, formulas can take care of the coloring.