Parametrized conditional formatting

Question

I am trying to dynamically set the Style of a row in Calc.

After giving up using a macro (see Changing cell background color in LibreOffice), I tried with Conditional Formatting rules (Format --> Conditional Formatting).

I would like to set a parametrized Range, so that the conditional formula is applied separately for each row.

I tried using the ADDRESS function to express ranges:

A1:C6    --->   ADDRESS(1,1):ADDRESS(6,3)

But that doesn't work. This reduces down to testing single cell functions, for instance:

    .------------------- -- -
    |  A  |  B  |  C  |  D
.----------------------- -- -
| 1 | aaa | bbb | ccc | 
.--------------------- -- -

[ok] =ADDRESS(1,3)
  \____$C$1

[ok] =CELL("contents", C1)
  \____ccc

[error] =CELL("contents", ADDRESS(1,3))
   \____#REF!

As you see, the last function gives a #REF! error (instead of ccc).

Any suggestion?

score 3 · Accepted Answer · answered Feb 20 '15 at 15:23

3

ADDRESS returns the reference as text. CELL expects a reference. So you need to translate the reference string using INDIRECT:

=CELL("CONTENTS", INDIRECT(ADDRESS(1,3)))

answered Feb 20 '15 at 15:23

tohuwawohu

13,268
4
42
61

1

Thanks @tohuwawohu. Now I can parametrize my conditional formatting applying a style "by-row" in my *formula* like: `INDIRECT(ADDRESS(ROW(), 7)) = 0`. – Campa Feb 23 '15 at 08:01

Parametrized conditional formatting

1 Answers1