Calc: Conditional formatting only works in one row?

[Bort]

I’m trying to get random, single, cells in different rows to change the cell background color based on the value of another cell. For example if the target data cell (cell L1) value is 1 I want cells B3, L3, B23, L23, B32, L32, B47, and L47 background color to change to Lime Green.

I’ve selected the cells holding CTRL, Format > Conditional > Created the condition but when I click OK and make cell L1’s value 1 the only cells that change are B3 and L3. None of the other cells in the condition change. Why???

Is this a bug or does it have to be done differently, or is it not possible to use conditional formatting on random cells? Do I have to make the same condition over and over again for each row I want the B and L colomn cells to change it?

[Wanderer]

At first: Have you used an absolute address like $L$3 in your condition or relative like L3.

I usually test my conditional first with one cell.
When this works i extend the range at the bottom line of the dialogue to something like A1:G8 but you can also give single cells like A1, B2, C3

[Bort]

I tried both relative and absolute cell addresses. Neither worked. For example this is what I tried in the cell range field of the conditional format dialog:

---

Attempt 1:

B2, K2, B23, K23, B31, K31, B54, L54

Attempt 2:

$B$2,$K$2, $B$23, $K$23, $B$31, $K$31, $B$54, $L$54

Both times it only did the conditional format in the row 2 cells. None of the other cells changed background color even though they were told to in the range field of the condition dialog.

---

Bug?

[anon73440385]

… what’s your condition? Did you really use Formula is $L$1=1? (as already pointed out by @Wanderer). If not, you missed the fact, that the algorithm adapts the condition according to shifts in range just like formulas get adapted when copying cell content (and this would be the answer to Why?)

Example: Cell range: B2;K2;B23;K23;B31;K31;B54;L54 and assume condition L1=1 and cell content of L1 is 1

Walk through the range of cells:

• 1st cell of range: B2 → condition: L1=1 → TRUE → Style applied
• 2nd cell of range: K2 which is 9 columns right of B2 → condition: U1=1 (column U is 9 columns right of L) - result/style unclear, depends on content of U1
• 3rd cell of range: B23 which is 21 rows below B2 → condition: L22=1 → result/style unclear, depends on content of L22
• … and so on

That’s how the Conditional Formatting algorithm works, if you use a condition with relative addressing and that’s why you may get the desired style applied to one single cell only (probably the very first one of your range).

PS: If B3 and L3 change the style, you probably use Formula is: $L1=1 (absolute in column, but relative in row)

[Zizi64]

@Bort Please upload your .ods format sample file here.