Prev: / keyboard key doesn't work in Excel 2007
Next: Selecting only specific rows for a Sort
From: Neil Pearce on 14 Apr 2010 09:59
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
From: JLatham on 14 Apr 2010 10:29
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,
on another sheet or even on another sheet that itself is hidden.


"Neil Pearce" wrote:

> 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
From: Pete_UK on 14 Apr 2010 10:34
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

On Apr 14, 2:59 pm, Neil Pearce <Neil.Pea...(a)Gleeds.co.uk> wrote:
> 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

 | 
Pages: 1
Prev: / keyboard key doesn't work in Excel 2007
Next: Selecting only specific rows for a Sort