Bug 84927 - Calc - Errors in function "Conditional Formatting"
Summary: Calc - Errors in function "Conditional Formatting"
Status: RESOLVED DUPLICATE of bug 86253
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected)
Hardware: All All
: high critical
Assignee: Not Assigned
Keywords: bibisected, regression
Depends on:
Reported: 2014-10-12 02:55 UTC by Llogic
Modified: 2015-12-15 11:03 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:

Sample ods file attached in case you need it for your testing / verification. (695.52 KB, application/vnd.oasis.opendocument.spreadsheet)
2014-10-12 02:55 UTC, Llogic

Note You need to log in before you can comment on or make changes to this bug.
Description Llogic 2014-10-12 02:55:47 UTC
Created attachment 107731 [details]
Sample ods file attached in case you need it for your testing / verification.

Happens in :
1. opensuse Factory - Libreoffice Version:, Build ID: 430m0(Build:2)
2. Debian Wheezy Backports - Libreoffice Version:, Build ID: 430m0(Build:2)

Both the libreoffice configuration is reset (rm -rf ~/.config/libreoffice) and retested to confirm that problem still exist.
# Sample ods file attached in case you need it for your testing / verification,

Formating :
Conditional formatting using Formula as described below:
For cell C2, if C2>B2 , change background colour to Green
For cell C2, if C2<B2 , change background colour to Red
# See worksheet named :Sample.png

Problem 1 :
Copy of formatting (is intermittent) sometimes does not work - conditional format is not copied.
You have to randomly try to find out for yourself all the possible variations. One reproducable example : 
1. Select C2:C31, which is conditionally formatted.
2. Copy format and paste to D2:D31, only D2 is formated as shown in image.
# See worksheet named : intermittent.png

Problem 2 :
Properly conditional formatted spreadsheet may lost some of its conditional formatting after saving and immediately reopen.
# See worksheet named : lost.png
Comment 1 Jacques Guilleron 2014-10-23 15:16:34 UTC
I Llogic,

Once set conditional formatting for C2, you can use Paintbrush to copy and paste it to the range you want.
Click on C2 to select the cell, click on paintbrush to copy the format and select the range you desire. Format is applied when you release mouse button.

Another way to do that:
Format > Conditional Format > Manage:
Select C2. Edit:
In the range windows insert ":"
and set K31, for example, or D31.
OK to apply our settings.
you can delete C3, C4, C5 and D3, D4, D5.
OK again.
The whole range from C2 to D31 ot K31 is formated.

Is there a problem to do this for you?

Comment 2 Llogic 2014-10-23 22:50:23 UTC
Hi Jacques, thanks for replying.

1. I've downloaded the file that I've previously uploaded and tried what you've suggested.
Direct link (https://bugs.freedesktop.org/attachment.cgi?id=107731)

Copy format using paintbrush as you've suggested.

Yes, the range is formated properly.
Save the file.

Re-open the file and found that the formatting is lost.

Reference Screnshot http://postimg.org/image/e5rtwaen7/
Noticed that after using the paintbrush for formatting, the conditional  formatting is visually correct.
However, select (example shown here, cell K23) and verify the conditional formatting criteria, it is not there.
Therefore when you save and reopen, the formatting is lost.

2. Testing with actual use case - daily update

 - For actual usage, data are added daily so the conditional formatting have to be done daily 
 - Also the conditional formatting for each row (of the same column) may be different.
Just like copying of value and formulas, can the copying of conditional formatting be done as a whole column ?

Using the same file that I've previously uploaded, copy conditional formatting from C2 to C2:C31
Conditional formatting is okay visually for C2:C31
Select column C and copy format using paintbrush.
Paste format to column D.

The conditional formatting is copied over to column D but not properly done.

The above information for your review.

Comment 3 Jacques Guilleron 2014-10-24 06:32:18 UTC
Hi Llogic,

You are right. Conditional format is applied but not recorded with paintbrush in LO Build ID: f9b3ad49d92181b0a1fe7e76f785a2c2cd0847d3
& Windows 7 Home Premium.

Works as expected with LO
Build ID: 92216be6ce13990b8ea6b6264c656d2bc1746401
TinderBox: Win-x86@42, Branch:libreoffice-4-2, Time: 2014-07-14_16:21:42

Set the Status to NEW, Hardware to ALL and Keywords to regression.


Comment 4 Llogic 2014-10-24 12:19:26 UTC
Hi Jacques, 

thanks for confirmation.

just curious, what is the next step?
Comment 5 Jacques Guilleron 2014-10-24 14:19:18 UTC
First version found with applied and non-recorded paintbrush:
LO Build ID: b7cfa1eab1cb1e94f71d6df6612b73f231d0bf92
These leaves no gap betwween the working version and the non-working one with hope this helps dev.

Next step: Wait for Markus advice.

Have a nice day,

Comment 6 Markus Mohrhard 2014-11-29 20:20:34 UTC
Looks like another duplicate of Bug 86253. I tested the sheet after fixing the bug and it seems that I can't reproduce the issue anymore.

Please retest as soon as the fix appears in a daily build.
Comment 7 Markus Mohrhard 2014-12-04 00:32:11 UTC

*** This bug has been marked as a duplicate of bug 86253 ***
Comment 8 Robinson Tryon (qubit) 2015-12-15 11:03:27 UTC
Migrating Whiteboard tags to Keywords: (bibisected)