2015 Archives by date, by thread · List index

# Re: [libreoffice-users] Conditional formatting question

```OK, in stead of an dynamic setting I reverted to manual setting (makes the sheet faster too).

I set autofilter on the column, selected all rows not empty, and set the background color...
Simple and fast.

Why do something dynamic if the data is not that dynamic.

On 3 jul. 2015, at 21:32, Rob Jasper wrote:

```
```Andreas & Brian,

thanks, that works great!

Now I have another followup question:

I already had a formatting for 2 colums where I set the number of digits behind the decimal based
on the formula MOD(F2,0.01)=0.
This to make a Euro/dfl denomination look like 0.10 and 0.15, while the real old values (e.g.
12.5 cents) display as 0.125

For those values which on have 2 digits after de decimal, the background color doesn't display..
If I look at the conditions the first one is the one just added (Range A2:AMJ3500; Formula
ISNUMBER(\$H2). This is followed by a hugh list in the style F4  — MOD(F4,0.01)=0
F20-F21 — MOD(F20,0.01)=0
Apparently the list breaks if rows are added... I changed the condition to MOD(\$F2,0.01=0) for
the range F2-F3500.

It seems that changing the number format conditionally overrides the set background color.

Result:
If Fx has the value n.nnn it has de correct background but the numberformat displayed is now n.nn
If Fx has the value n.nn the background is gone

Am I missing something here?
Is this expected behavior?

Thanks,
Rob.

On 3 jul. 2015, at 19:17, Brian Barker wrote:

```
```At 16:23 03/07/2015 +0200, Rob Jasper wrote:
```
```I am looking on a way to set the background color of a full row if one specific field in the
row has a value. [Example: C3 = 3.] I looked at conditional formatting but I don't see how to
format the complete line based on one value in the line.
```
```
By "having a value", do you mean the specific example value of 3, or do you mean having value as
distinct from not having a value (whatever that means)?

o Select the relevant rows.
o Go to Format | Conditional Formatting... .
o For Condition 1, select "Formula is" from the drop-down menu.
o In the box, enter INDIRECT("\$C"&ROW())=3 - or perhaps INDIRECT("\$C"&ROW())<>0 .
o For Cell Style, select your cell style with the appropriate background colour (or click New
Style... and create a style on the fly).
o OK.

I trust this helps.

Brian Barker

--
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

```
```

--
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

```
```

--
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted
```