Download it now!
Bug 99808 - Conditional FORMATTING of connected cells: Background fills only first cell
Summary: Conditional FORMATTING of connected cells: Background fills only first cell
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
5.1.2.2 release
Hardware: All Windows (All)
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Conditional-Formatting
  Show dependency treegraph
 
Reported: 2016-05-12 18:32 UTC by libreoffice
Modified: 2019-07-30 10:17 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Attachments
Screenshot of the error in calc (6.78 KB, image/png)
2016-05-12 18:32 UTC, libreoffice
Details
Test At Step 9 (38.51 KB, image/png)
2016-05-12 20:02 UTC, Theodore Matula Jr.
Details
test after step 11 (35.83 KB, image/png)
2016-05-12 20:03 UTC, Theodore Matula Jr.
Details
merged cells color bars (21.34 KB, image/png)
2016-05-12 20:52 UTC, libreoffice
Details

Note You need to log in before you can comment on or make changes to this bug.
Description libreoffice 2016-05-12 18:32:44 UTC
Created attachment 125015 [details]
Screenshot of the error in calc

I connected 4 times 6 cells vertically and added a conditional formatting over these connected cells that adjusts the background color of the cells depending on their value.

In Libreoffice Calc, only the background of the first original cell is adjusted (see screenshot). If I export an xlsx in Calc and open the file in Microsoft Excel, Excel adjusts the background color of the complete cell, not only the first.
Comment 1 Theodore Matula Jr. 2016-05-12 20:02:49 UTC
Created attachment 125016 [details]
Test At Step 9

Summary: I can confirm this on Version: 5.1.3.2 in Windows 10, but with some stipulations. When applying a background as conditional formatting to a block of merged cells, only the first "original" cell will get the background applied. However, If you double click the cell and then click and other cell, the formatting will be applied to the entire merged block. Though, this may be exclusive to my version of LibreOffice, so I do not know If this "fix" works for earlier versions.

Steps:
1. Open a new LibreOffice Calc spreadsheet.
2. Highlight cells A1-A5 and right click on the cells. (I used cells D1-D5, but it doesn't really matter)
3. Click Merge cells
4. Click the new block of merged cells and type 0 in it.
5. From the Menu, click Format>Conditional Formatting>Condition...
6. Click add to add a new condition
7. In this new Condition, set the condition to "Cell value is" "Equal to" "0"
8. From the drop down menu next to Apply Style, click New Style. Click the background tab, choose any non-white color, and click OK. 
9. Click OK to close the conditional formatting window.

Expected Result: The entire merged cell's background color is what you selected in the conditional formatting

Actual Result: Only the first "original cell" in the merged block has the formatting applied to it.

After Step 9, you can quickly fix the issue by the following steps:
10. Double click the merged cell. It should turn white and the text input cursor should appear in it. 
11. Click on any cell outside of the merged cell block. the background color should now be applied to the entire merged block.
Comment 2 Theodore Matula Jr. 2016-05-12 20:03:21 UTC
Created attachment 125017 [details]
test after step 11
Comment 3 libreoffice 2016-05-12 20:52:02 UTC
Created attachment 125020 [details]
merged cells color bars
Comment 4 libreoffice 2016-05-12 20:52:27 UTC
I can confirm your workaround on this simple example. But it does not work with a color bar (is this the right english term? gradient?).

1. Open a new LibreOffice Calc spreadsheet.
2. Enter 1 in A1, 2 in B1
3. Highlight cells A1-A2 and right click on the cells and click merge cells
4. Highlight cells b1-B2 and right click on the cells and click merge cells
5. Highlight the two merged cells
6. From the Menu, click Format>Conditional Formatting>Color bars 
7. Keep the default and click ok to close the conditional formatting window.

Expected Result: The two merged cell's should be entirely have a background color 

Actual Result: Only the first "original cell" in the merged blocks has the formatting applied to it.

10. Double click one of the merged cells. It should turn white and the text input cursor should appear in it. 
11. Click on any cell outside of the merged cell block. 

Result: No change, still only the first "original cell" has a different background color.

Screenshot just added.
Comment 5 Theodore Matula Jr. 2016-05-12 21:24:20 UTC
Upon further testing, it seems that if a merged cell has "color scale" condition formatting applied to it and then removed, a subsequent "conditional" condition formatting work as intended.

Steps:
1. Open a new LibreOffice Calc spreadsheet.
2. Enter 1 in A1, 2 in B1
3. Highlight cells A1-A2 and right click on the cells and click merge cells
4. Highlight cells b1-B2 and right click on the cells and click merge cells
5. Highlight the two merged cells
6. From the Menu, click Format>Conditional Formatting>Color Scale
7. Keep the default and click ok to close the conditional formatting window.

8. From the Menu, click Format>Conditional Formatting>Manage. Select the newly made entry and click remove, then ok
9. From the Menu, click Format>Conditional Formatting>Condition...
10. Click add. In the new window, set the condition to "Cell value is" "Not equal to" "0"
11. From the drop down menu next to Apply Style, click New Style. Click the background tab, choose any non-white color, and click OK. 
12. Click OK to close the conditional formatting window.
Comment 6 QA Administrators 2018-01-16 03:29:19 UTC Comment hidden (obsolete)
Comment 7 Ludvig Svenonius 2018-02-24 12:30:50 UTC
This bug still remains, I just encountered it in a fresh install of the most recent version, more specifically:
Version: 6.0.1.1 (x64)
Build ID: 60bfb1526849283ce2491346ed2aa51c465abfe6
CPU threads: 4; OS: Windows 10.0; UI render: default; 
Locale: en-GB (en_GB); Calc: CL
Comment 9 QA Administrators 2019-07-30 03:15:52 UTC Comment hidden (obsolete)
Comment 10 libreoffice 2019-07-30 10:17:49 UTC
Just tried with the following LibreOffice version and this bug is still there:

Version: 6.2.5.2 (x64)
Build-ID: 1ec314fa52f458adc18c4f025c545a4e8b22c159
CPU-Threads: 4; BS: Windows 6.1; UI-Render: Standard; VCL: win; 
Gebietsschema: de-DE (de_DE); UI-Sprache: de-DE
Calc: threaded