Issue 108013 - No screen refresh for conditional formatting with formulas
Summary: No screen refresh for conditional formatting with formulas
Alias: None
Product: Calc
Classification: Application
Component: formatting (show other issues)
Version: OOO310m9
Hardware: PC All
: P3 Trivial (vote)
Target Milestone: ---
Assignee: AOO issues mailing list
QA Contact:
Keywords: oooqa
: 96721 (view as issue list)
Depends on:
Reported: 2009-12-30 17:51 UTC by markymark7
Modified: 2014-01-06 09:36 UTC (History)
4 users (show)

See Also:
Issue Type: DEFECT
Latest Confirmation in: ---
Developer Difficulty: ---

Sample spreadsheet showing problem (8.31 KB, application/vnd.oasis.opendocument.spreadsheet)
2009-12-30 17:53 UTC, markymark7
no flags Details
test document with AND and SIN in condition (11.03 KB, application/vnd.oasis.opendocument.spreadsheet)
2010-01-01 19:04 UTC, Regina Henschel
no flags Details
Conditionnal formating with Indirect function (12.93 KB, application/octet-stream)
2014-01-06 09:35 UTC, jeffooo
no flags Details

Note You need to log in before you can comment on or make changes to this issue.
Description markymark7 2009-12-30 17:51:58 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


Each of the cells B3...B6 have a a conditional format set to the formula like
this one in B5:


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.

Comment 1 markymark7 2009-12-30 17:53:31 UTC
Created attachment 66920 [details]
Sample spreadsheet showing problem
Comment 2 Oliver Brinzing 2009-12-31 07:50:17 UTC
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)

Comment 3 Oliver Brinzing 2009-12-31 09:59:06 UTC
aah, having a closer look, i can see the problem ;-)

but have you already tried to change the formula's like:


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:
Comment 4 markymark7 2009-12-31 22:03:42 UTC
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.
Comment 5 Regina Henschel 2009-12-31 22:43:11 UTC
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.
Comment 6 markymark7 2010-01-01 04:24:26 UTC
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" ...

Comment 7 Regina Henschel 2010-01-01 19:02:54 UTC
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.
Comment 8 Regina Henschel 2010-01-01 19:04:45 UTC
Created attachment 66934 [details]
test document with AND and SIN in condition
Comment 9 Regina Henschel 2010-01-01 19:07:35 UTC
*** Issue 96721 has been marked as a duplicate of this issue. ***
Comment 10 Oliver Brinzing 2010-01-02 09:00:28 UTC
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.
Comment 11 markymark7 2010-01-02 17:15:10 UTC
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.
Comment 12 jeffooo 2014-01-06 09:35:48 UTC
Created attachment 82225 [details]
Conditionnal formating with Indirect function
Comment 13 jeffooo 2014-01-06 09:36:47 UTC

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.