Conditional Formatting: Fill Cell if Formula Overwritten

[Neil Pearce]

Dear all,

Cell A1 contains a formula.

How would I go about conditonally formatting A1 such that the cell will be
filled if the formula is overwritten by a hard number?


Thanks in advance,

Neil

 

[JLatham]

Any reason not to lock the cell and use worksheet protection to keep people
from accidentally overtyping the formula?

Here's one option, I'm sure there will be others.

Start in your cell of concern (lets use A1), it has a formula in it such as
=A2+B3
In another cell somewhere put in the same formula, but subtract 1 from the
result as: =A2+B3-1
Let's say that was cell J1.
Then for your conditional format you can use:
Cell Value and 'not equal to' and enter =$J$1+1

This doesn't guarantee instant recognition of the overtyping. Lets say that
the answer from the formula is 27 and someone types in 27. The format will
not change, but as soon as a value in the precedent cells (A2 or B3) changes,
then you'll get the alert.

Another way would be to use VBA and the worksheet's _Change() event to
monitor the cell. And as I said, someone may come along and offer a method
that doesn't use the 2nd cell as I did. BTW: that second cell can be hidden,

 

[Pete_UK]

John Walkenbach describes a method here:

http://j-walk.com/ss/excel/usertips/tip045.htm

Actually, his approach is the opposite of what you want, in that he
colours a cell that contains a formula, but perhaps you can modify the
approach.

An alternative is to lock the cells with formulae in and protect the
sheet (with or without a password) to prevent accidental deletion/
overwriting of the cells containing formulae.

Hope this helps.

Pete