Conditional Formatting not working with list cell

[libreoffiguess]

LO Version: 5.1.6.2
Build ID: 1:5.1.6~rc2-0ubuntu1~xenial3
OS: Ubuntu 16.04 LTS

I have a Calc sheet where I want the formatting of a given row to be dependent on the value of a specific cell within that row. For some reason, I can get this to happen with the STYLE() formula, but not with the Conditional Formatting setting (which would be preferable so that I don’t have to slap a STYLE() on every single row).

The specific cell being used to determine the formatting has text content restricted by a List criteria: the contents can either be “Active” or “Inactive”. If the cell’s contents are “Inactive”, I want the “Inactive” formatting style to be applied to the row. If the contents are “Active”, I want the “Active” formatting style applied.

If I apply the following STYLE() formula to a cell within the row, it executes just fine and behaves as expected:

STYLE(IF(C4="Inactive";"Inactive";"Active"))

However, if I go to Conditional Formatting and use the following details:

Condition 1
Formula is C4="Inactive"
Apply style Inactive
Cell Range: A4:Z4

and

Condition 2
Formula is C4="Active"
Apply style Active
Cell Range: A4:Z4

Nothing happens. No formatting is applied, and toggling the value of C4 doesn’t make a difference.

I’ve tried setting “Formula is” to IF(C4="Inactive"), C4=="Inactive", and anything else I can think of, but nada. What’s going on here?

[erAck]

When using a formula in conditional formatting, all cell references obey the same relative/absolute reference rules as when used in cell formulas or named expressions. Specifically the relative reference is created between the cell where the conditional format is defined and the referenced cell. For example, defining C4=“Inactive” in a conditional format created on cell C4 means to always inspect the “current” cell, defining it on cell C5 means to look at the cell above, and so on. In your case you probably defined the conditional formatting with the selection when the cell cursor was on A4 (or Z4) so it always looks two columns to the right (or 23 columns to the left) in the same row.

If you want to always inspect cell C4 regardless of the position of the conditional format then use an absolute cell reference, i.e. $C$4 or $C4 to apply it to multiple rows where in reach row the value to inspect is in column C.

[libreoffiguess]

Augh, that did it, thank you so much!