Conditional Formatting: Fill Cell if Formula Overwritten

N

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
 
J

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,
 
P

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top