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+unsubscr...@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+unsubscr...@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+unsubscr...@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