Apache OpenOffice (AOO) Bugzilla – Issue 108013
No screen refresh for conditional formatting with formulas
Last modified: 2014-01-06 09:36:47 UTC
I have a spreadsheet with 4 cells, B3...B6, each cell has a value of 0 or 1. There is a formula in cell C3 of =SUM(B3:B6) Each of the cells B3...B6 have a a conditional format set to the formula like this one in B5: AND($Sheet1.$C$3>1;B5=1) It uses a style to set the cell to a style using a Light Red font. If I have a 1 in cell B3, and the rest are 0, then I set B6 to be 1, B6 gets formatted correctly, but B3 formatting does not get updated to Light Red, unless I edit B3 and re-enter a 1. Hitting F9 to recalculate does not fix the formatting. Also, if I edit cells to end up with just one cell containing a 1, the cell still containing a 1 is still red. Example: http://www.meekmark.com/ConditionalFormattingNotRecalcing.zip
Created attachment 66920 [details] Sample spreadsheet showing problem
imho everything is ok - to see the Light Red font from your style "ErrorFlag" you have to switch the cell highlighting off (see menu "view" for the entry)
aah, having a closer look, i can see the problem ;-) but have you already tried to change the formula's like: AND($Sheet1.$C$3>1;B3=1;RAND()>=0) RAND() should *always* be calculated ... for example, this will show the sheetname in a cell and update the cell content automatically if the sheet is renamed: =MID(CELL("FILENAME");FIND("#$";CELL("FILENAME")&0*RAND())+2;256)
I tried adding the rand() trick in the formula in the conditional formatting dialog box, and that did not seem to help. I will mention that in the original spreadsheet where I encountered the problem, when I added some additional formulas that reference the cells that have the conditional formatting, it works OK.
I notice that the conditional formatting is OK, but the screen is not refreshed. Press Ctrl+Shift+R or scroll up and down to refresh the screen. Then it should show the right color. I use OOo3.2RC1 on WinXP.
That work-around of Ctrl-Shift-R or scrolling to refresh does work for me too. So now I suppose this summary could be "Conditional formatting not being reflected until screll is refreshed" ...
If the conditional formatting uses "Formula is", for some functions the screen does not refresh, when the cell gets a new style. I see the defect with AND and SIN, issue 96721 reports it for INDIRECT. If you test it with a new spreadsheet, make sure, that the refresh is not done because of other reasons.
Created attachment 66934 [details] test document with AND and SIN in condition
*** Issue 96721 has been marked as a duplicate of this issue. ***
@regina do you think this can be seen as a showstopper for OO32? imho at least (F9) should redraw the screen. i just found that (Strg)(Shift) F9 (recalc all sheets) will update the screen too.
I agree that a good short-term fix would be to have F9 refresh the screen, as that is the first key I hit (and hit, and hit...) when I didn't see the conditional formatting applied. For the root cause, I'm wondering (without having even glanced at the code) whether having a conditional format applied on a cell could have a flag set to indicate that cell needs to be always rendered upon any update anywhere on the spreadsheet.
Created attachment 82225 [details] Conditionnal formating with Indirect function
Hello, Hello (In reply to Regina Henschel from comment #7) > issue 96721 reports it for INDIRECT. Another way to reproduce bad refresh : In the attachment "Conditionnal formating with Indirect function", select C2 and just right-clik > align-center for good refresh. Regards