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?

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" <[email protected]> wrote in message
news:[email protected]...
>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?

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" <[email protected]> wrote in message
> news:[email protected]...
> >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?
>
>
>

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

"Rob" <[email protected]> wrote in message
news:[email protected]...
>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?

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 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
>
> "Rob" <[email protected]> wrote in message
> news:[email protected]...
> >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?
>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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 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
>>
>> "Rob" <[email protected]> wrote in message
>> news:[email protected]...
>> >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?
>>
>>
>>

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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 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
> >>
> >> "Rob" <[email protected]> wrote in message
> >> news:[email protected]...
> >> >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?
> >>
> >>
> >>
>
>
>