format a cell if the formula is removed
 

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?

format a cell if the formula is removed
 

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

"Rob" wrote in message
...
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?

format a cell if the formula is removed
 

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.

"Barb Reinhardt" wrote:

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

"Rob" wrote in message
...
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?

format a cell if the formula is removed
 

Hi!

Here's one way:

Create this named formula:

Goto InsertNameDefine
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 FormatConditional 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

"Rob" wrote in message
...
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?

format a cell if the formula is removed
 

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!!!


"Biff" wrote:

Hi!

Here's one way:

Create this named formula:

Goto InsertNameDefine
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 FormatConditional 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

"Rob" wrote in message
...
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?

format a cell if the formula is removed
 

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

"Rob" wrote in message
...
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!!!


"Biff" wrote:

Hi!

Here's one way:

Create this named formula:

Goto InsertNameDefine
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 FormatConditional 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

"Rob" wrote in message
...
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?

format a cell if the formula is removed
 

Excellent, thanks a lot.

"Biff" wrote:

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

"Rob" wrote in message
...
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!!!


"Biff" wrote:

Hi!

Here's one way:

Create this named formula:

Goto InsertNameDefine
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 FormatConditional 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

"Rob" wrote in message
...
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?