Conditional formatting does not change when values change. ??

[Guest]

My spreadsheet was created w an earlier version of XL. Now with Excel 2007
the conditional formatting does not change when one of the values involved
changes.

Example: The spreadsheet calculates a total value for something and places
it in v1. If that value is entered in a specific cell (example: N86), then
N86 should turn yellow. If the value entered in N86 is less than that in v1
it should turn red, and if greater than that in v1, it should be green. In
earlier version of XL that was working fine.

In Excel 2007, if I enter the value in N86 which matches the value in V1, it
correctly turns yellow. But, if I change the value in N86 (say to something
greater than the value of V1, it stays yellow and does not pick up the other
formatting(ie: does not turn green) - it just stays yellow. It just looks
like it is not dynamically applying the conditional formatting.

Thoughts please? Thanks in advance.

 

[David Biddulph]

One thing is to check that your calculation mode is set to automatic.

Also worth checking exactly what formula you've got in CF for each of the
conditions. Excel can often throw in unwanted quote marks or absolute
references where you haven't asked for them. [You can usually avoid this by
typing in the = sign at the beginning of the formula, rather than letting
excrel supply it.]

If you're still struggling, copy each of the conditions from your CF
formulae to here in the group (don't retype them , just copy and paste), in
the order in which they are specified (remembering that it will test them in
that order until one of the conditions is satisfied), tell us whether they
are specified as "Cell Value Is" with a relationship, or as "Formula Is",
and tell us what values you think you've got in the relevant data cells.

Also worth checking whether you've got numbers or text values in the various
cells. Try ISNUMBER(cellref) and ISTEXT(cellref) for each of the relevant
cells.

 

[Guest]

Look up Conditional formatting in Help and take particular notice of the
'Stop if true' feature. When you work your way through this you might resolve
your problem.

Regards,

OssieMac

 

[Guest]

Thanks David - indeed calc mode was automatic, but there were unwanted quote
marks. I retyped the formulae and all is working as it should.
Thanks for your support! Texana

One thing is to check that your calculation mode is set to automatic.

Also worth checking exactly what formula you've got in CF for each of the
conditions. Excel can often throw in unwanted quote marks or absolute
references where you haven't asked for them. [You can usually avoid this by
typing in the = sign at the beginning of the formula, rather than letting
excrel supply it.]

If you're still struggling, copy each of the conditions from your CF
formulae to here in the group (don't retype them , just copy and paste), in
the order in which they are specified (remembering that it will test them in
that order until one of the conditions is satisfied), tell us whether they
are specified as "Cell Value Is" with a relationship, or as "Formula Is",
and tell us what values you think you've got in the relevant data cells.

Also worth checking whether you've got numbers or text values in the various
cells. Try ISNUMBER(cellref) and ISTEXT(cellref) for each of the relevant
cells.
--
David Biddulph

My spreadsheet was created w an earlier version of XL. Now with Excel
2007
the conditional formatting does not change when one of the values involved
changes.

Example: The spreadsheet calculates a total value for something and
places
it in v1. If that value is entered in a specific cell (example: N86),
then
N86 should turn yellow. If the value entered in N86 is less than that in
v1
it should turn red, and if greater than that in v1, it should be green.
In
earlier version of XL that was working fine.

In Excel 2007, if I enter the value in N86 which matches the value in V1,
it
correctly turns yellow. But, if I change the value in N86 (say to
something
greater than the value of V1, it stays yellow and does not pick up the
other
formatting(ie: does not turn green) - it just stays yellow. It just
looks
like it is not dynamically applying the conditional formatting.

Thoughts please? Thanks in advance.

 

[Guest]

Thanks for your assistance. It was the darned extra quotes that snuck into
the formulae. Regards, Texana