Not working: Conditional Formatting

[Mel]

Excel 2007

Conditional formatting is not changing formatting unless something is
triggered to refresh the screen.

For example, cell D5 has the following rule: =G5<>"" format with blue
fill. When I manually type an entry into G5, D5 fills properly. But if
I delete (delete, not space) the G5 entry, the blue fill is not
removed from D5. BUT... when I task swap to another program and come
back, it takes effect. D5 is then restored to no fill.

So, conditional formatting is formulated correctly and I'm doing
things correctly, but Excel's screen isn't refreshing automatically -
only when I pop over to another program and come back.

The only time it works right is if I manually make the entry in G5. if
I paste an entry into a previously blank G5, nothing happens to D5.
Also if I autofill down or CTRL+D to populate and entry into G5, D5 in
unaffected. BUT... then all these work correctly once I task swap to
another program and come back. The screen somehow refreshes to correct
its conditional formatting.

Is there a way to fix this?

-Melina

 

[Gord Dibben]

Turn your calculation mode to automatic.

Button>Excel Options>Formulas>Calculation Options.


Gord Dibben MS Excel MVP

 

[Mel]

It already was set to automatic. Formulas calculate fine - it's just
the conditional formatting having the problem.

 

[Mel]

Here are two other unresolved threads where users had the same issue
about which I'm writing.

http://tinyurl.com/cf-issue01
http://tinyurl.com/cf-issue02

Is this an Excel bug?

-Melina

 

[Mel]

Someone suggested my video card is at fault for not refreshing fast
enough. But it can't be. The same conditional formatting works iin
other workbooks, which are much larger, and it works fine in v2003,
not to mention I have much video demands with other software I use and
the video card performs well.

The workbooks in questions were created in v2003 with conditional
formatting. When built in 2007 the same formatting works fine. (Again
showing it's not my machine.) But when reprogramming the 2003
workbook's conditional formatting in 2007 and using 2007 tools to do
it, the same problem occurs.

So, what's the issue with having saved a 2003 workbook in 2007 and the
conditional formatting will not refresh correctly? Obviously, I'm not
the only one to have discovered this. Is it a known bug?

Is the the right group to be asking this question?

Thanks,
Melina

 

[Rico Suave]

Oh it's a known bug all right. Good luck getting any of the office "experts"
to respond though. They know the problem and have no solution. Instead they
like to think it's your fault. It must be a bad formula or auto-calc. Don't
know how many times I've seen that bit of advice here. 2003 and 2007 don't
play nicely together where CF is concerned. I've had luck writing
Worksheet_Change code instead of the CF tool, but this is an issue I've
fought with for the past few years with no resolution through CF.

 

[Randy Maglio]

Ok, I had a similar problem. All of a sudden I could not get my conditional formating to work.

I discovered the problem was due to one of my cells having an error (#value) condition. This seems to have interfered with the conditional formatting rule.

I just put in a 0 in place of the formula cell causing the value error and all my conditional formatting rules worked.

Had nothing to do with video cards.