format a cell if the formula is removed

R

Rob

I want to apply a conditional format to let someone know that the formula in
a cell has been overwritten. Is there a way to do this?
 
B

Barb Reinhardt

Can you clarify? Do you want a CF under the following conditions:

Formula replaced with another formula
Formula replaced with something besides a formula?

I can think of a way if the formula has been removed.

Put this in for your conditional format

FORMULA IS
=ISNA(SEARCH("=",A1))=FALSE
 
R

Rob

Well, what I want to do is to give an individual the ability to overwrite a
formula if they want to put in a value in a particular cell. I want the cell
background to turn red or whatever if the cell formula is overwritten.
 
B

Biff

Hi!

Here's one way:

Create this named formula:

Goto Insert>Name>Define
Name: CellHasFormula
Refers to: =GET.CELL(48,INDIRECT("RC",FALSE))=FALSE
OK

Now, select the cell you want to format, assume that's cell A1
Goto Format>Conditional Formatting
Formula is: =AND(CellHasFormula,A1<>"")
Select the format style desired
OK out

When the cell contains a formula no formatting is applied. When the cell
contains a constant the format is applied.

Biff
 
R

Rob

Biff:

That works beautifully!!! What are we saying with this statement anyway
=GET.CELL(48,INDIRECT("RC",FALSE))=FALSE. I don't quite get the logic?

Thanks a lot, too!!!
 
B

Biff

Hi!

GET.CELL() is from the old Excel macro language. That macro language has
been replaced by VBA but it's still supported.

48 is the argument index number for a formula in a cell.

In order for these macro language functions to work they have to be called
from named formulas. Thus the reason to create the named formula. Since this
has to be called from a named formula we can't use absolute cell referencing
so the need for R1C1 referencing via the Indirect function.

INDIRECT("RC"....) refers to the target cell of the conditional formatting.

=GET.CELL(48,INDIRECT("RC",FALSE))

This would return TRUE if the cell has a formula. Since your criteria is the
opposite and since CF is based on a condition of TRUE, we need to test for a
condition of FALSE, thus:

=GET.CELL(48,INDIRECT("RC",FALSE))=FALSE

Biff
 
R

Rob

Excellent, thanks a lot.

Biff said:
Hi!

GET.CELL() is from the old Excel macro language. That macro language has
been replaced by VBA but it's still supported.

48 is the argument index number for a formula in a cell.

In order for these macro language functions to work they have to be called
from named formulas. Thus the reason to create the named formula. Since this
has to be called from a named formula we can't use absolute cell referencing
so the need for R1C1 referencing via the Indirect function.

INDIRECT("RC"....) refers to the target cell of the conditional formatting.

=GET.CELL(48,INDIRECT("RC",FALSE))

This would return TRUE if the cell has a formula. Since your criteria is the
opposite and since CF is based on a condition of TRUE, we need to test for a
condition of FALSE, thus:

=GET.CELL(48,INDIRECT("RC",FALSE))=FALSE

Biff
Click to expand...
 
Top