Cell background has to be changed, if the cell doesn't contain formula.

I have tried implementing it in following ways

I have tried changing the cell back ground using Conditional formatting with
User Defined function(to check if cell has formula or value); but, adding
User Defined function degrades the performance of the template.
I have tried changing background by executing VSTO code on Range Change
event. But, undo stack is lost and user is unable to undo the changes
I also tried using Excel 4.0 macro..please see the implementation below..

You can use Excel 4 macro in a defined name formula to check if cell has a
formula, using you example
do insert>name>define, in the source box put

=GET.CELL(6,Sheet1!$A1)

and in the name box type in something descriptive like

FormulaIs

click OK


Select A1:A3
with A1 as the active cell, do format>conditional formatting, select formula
is
and use

=LEFT(FormulaIs)="="

or in a pedagogic manner

=LEFT(FormulaIs,1)="="

you can actually leave out 1 if you just want one character

now click the format button and select format and click OK twice

But, I cannot use this method, because I get a security warning for Excel
4.0 macros when I open the template.


Is there any other way of checking if a cell contains formula in conditional
formatting?

An unpleasant solution would be to purchase MS coditional format assistant
this adds the option of "contains" to the conditional formatting list
(amongst others) so then you could change format based on if it contains an =
sign as with a formula for instance. Its an unplesant solution cos it costs
24 bucks

"Haritha Kolla" wrote:

> Cell background has to be changed, if the cell doesn't contain formula.
>
> I have tried implementing it in following ways
>
> I have tried changing the cell back ground using Conditional formatting with
> User Defined function(to check if cell has formula or value); but, adding
> User Defined function degrades the performance of the template.
> I have tried changing background by executing VSTO code on Range Change
> event. But, undo stack is lost and user is unable to undo the changes
> I also tried using Excel 4.0 macro..please see the implementation below..
>
> You can use Excel 4 macro in a defined name formula to check if cell has a
> formula, using you example
> do insert>name>define, in the source box put
>
> =GET.CELL(6,Sheet1!$A1)
>
> and in the name box type in something descriptive like
>
> FormulaIs
>
> click OK
>
>
> Select A1:A3
> with A1 as the active cell, do format>conditional formatting, select formula
> is
> and use
>
> =LEFT(FormulaIs)="="
>
> or in a pedagogic manner
>
> =LEFT(FormulaIs,1)="="
>
> you can actually leave out 1 if you just want one character
>
> now click the format button and select format and click OK twice
>
> But, I cannot use this method, because I get a security warning for Excel
> 4.0 macros when I open the template.
>
>
> Is there any other way of checking if a cell contains formula in conditional
> formatting?
>

Thanks Atishoo for the reply. But, I cannot purchase MS Conditional format
assistant.

"Atishoo" wrote:

> An unpleasant solution would be to purchase MS coditional format assistant
> this adds the option of "contains" to the conditional formatting list
> (amongst others) so then you could change format based on if it contains an =
> sign as with a formula for instance. Its an unplesant solution cos it costs
> 24 bucks
>
> "Haritha Kolla" wrote:
>
> > Cell background has to be changed, if the cell doesn't contain formula.
> >
> > I have tried implementing it in following ways
> >
> > I have tried changing the cell back ground using Conditional formatting with
> > User Defined function(to check if cell has formula or value); but, adding
> > User Defined function degrades the performance of the template.
> > I have tried changing background by executing VSTO code on Range Change
> > event. But, undo stack is lost and user is unable to undo the changes
> > I also tried using Excel 4.0 macro..please see the implementation below..
> >
> > You can use Excel 4 macro in a defined name formula to check if cell has a
> > formula, using you example
> > do insert>name>define, in the source box put
> >
> > =GET.CELL(6,Sheet1!$A1)
> >
> > and in the name box type in something descriptive like
> >
> > FormulaIs
> >
> > click OK
> >
> >
> > Select A1:A3
> > with A1 as the active cell, do format>conditional formatting, select formula
> > is
> > and use
> >
> > =LEFT(FormulaIs)="="
> >
> > or in a pedagogic manner
> >
> > =LEFT(FormulaIs,1)="="
> >
> > you can actually leave out 1 if you just want one character
> >
> > now click the format button and select format and click OK twice
> >
> > But, I cannot use this method, because I get a security warning for Excel
> > 4.0 macros when I open the template.
> >
> >
> > Is there any other way of checking if a cell contains formula in conditional
> > formatting?
> >

On Tue, 28 Jul 2009 02:15:01 -0700, Haritha Kolla wrote:

> Cell background has to be changed, if the cell doesn't contain formula.
>
> I have tried implementing it in following ways
>
> I have tried changing the cell back ground using Conditional formatting with
> User Defined function(to check if cell has formula or value); but, adding
> User Defined function degrades the performance of the template.
> I have tried changing background by executing VSTO code on Range Change
> event. But, undo stack is lost and user is unable to undo the changes
> I also tried using Excel 4.0 macro..please see the implementation below..
>
> You can use Excel 4 macro in a defined name formula to check if cell has a
> formula, using you example
> do insert>name>define, in the source box put
>
> =GET.CELL(6,Sheet1!$A1)
>
> and in the name box type in something descriptive like
>
> FormulaIs
>
> click OK
>
>
> Select A1:A3
> with A1 as the active cell, do format>conditional formatting, select formula
> is
> and use
>
> =LEFT(FormulaIs)="="
>
> or in a pedagogic manner
>
> =LEFT(FormulaIs,1)="="
>
> you can actually leave out 1 if you just want one character
>
> now click the format button and select format and click OK twice
>
> But, I cannot use this method, because I get a security warning for Excel
> 4.0 macros when I open the template.
>
>
> Is there any other way of checking if a cell contains formula in conditional
> formatting?

Do you have to use Conditional formatting to do this? Not VBA?

Salgud Thanks for the reply...
If user overwrites formula in the cell, the back ground should be changed to
different color. But, if we implement this in VBA code or VSTO on cell
change, the undo stack will be lost...and user will not be able to undo his
changes. That is the reason why I wanted to handle it using Conditional
Formatting..

"salgud" wrote:

> On Tue, 28 Jul 2009 02:15:01 -0700, Haritha Kolla wrote:
>
> > Cell background has to be changed, if the cell doesn't contain formula.
> >
> > I have tried implementing it in following ways
> >
> > I have tried changing the cell back ground using Conditional formatting with
> > User Defined function(to check if cell has formula or value); but, adding
> > User Defined function degrades the performance of the template.
> > I have tried changing background by executing VSTO code on Range Change
> > event. But, undo stack is lost and user is unable to undo the changes
> > I also tried using Excel 4.0 macro..please see the implementation below..
> >
> > You can use Excel 4 macro in a defined name formula to check if cell has a
> > formula, using you example
> > do insert>name>define, in the source box put
> >
> > =GET.CELL(6,Sheet1!$A1)
> >
> > and in the name box type in something descriptive like
> >
> > FormulaIs
> >
> > click OK
> >
> >
> > Select A1:A3
> > with A1 as the active cell, do format>conditional formatting, select formula
> > is
> > and use
> >
> > =LEFT(FormulaIs)="="
> >
> > or in a pedagogic manner
> >
> > =LEFT(FormulaIs,1)="="
> >
> > you can actually leave out 1 if you just want one character
> >
> > now click the format button and select format and click OK twice
> >
> > But, I cannot use this method, because I get a security warning for Excel
> > 4.0 macros when I open the template.
> >
> >
> > Is there any other way of checking if a cell contains formula in conditional
> > formatting?
>
> Do you have to use Conditional formatting to do this? Not VBA?
>

What about saving the file under a modified name so the user can open the
saved file to replicate the undo? Of course, wouldn't work if you need the
full stack.


On Tue, 28 Jul 2009 20:53:58 -0700, Haritha Kolla wrote:

> Salgud Thanks for the reply...
> If user overwrites formula in the cell, the back ground should be changed to
> different color. But, if we implement this in VBA code or VSTO on cell
> change, the undo stack will be lost...and user will not be able to undo his
> changes. That is the reason why I wanted to handle it using Conditional
> Formatting..
>
> "salgud" wrote:
>
>> On Tue, 28 Jul 2009 02:15:01 -0700, Haritha Kolla wrote:
>>
>>> Cell background has to be changed, if the cell doesn't contain formula.
>>>
>>> I have tried implementing it in following ways
>>>
>>> I have tried changing the cell back ground using Conditional formatting with
>>> User Defined function(to check if cell has formula or value); but, adding
>>> User Defined function degrades the performance of the template.
>>> I have tried changing background by executing VSTO code on Range Change
>>> event. But, undo stack is lost and user is unable to undo the changes
>>> I also tried using Excel 4.0 macro..please see the implementation below..
>>>
>>> You can use Excel 4 macro in a defined name formula to check if cell has a
>>> formula, using you example
>>> do insert>name>define, in the source box put
>>>
>>> =GET.CELL(6,Sheet1!$A1)
>>>
>>> and in the name box type in something descriptive like
>>>
>>> FormulaIs
>>>
>>> click OK
>>>
>>>
>>> Select A1:A3
>>> with A1 as the active cell, do format>conditional formatting, select formula
>>> is
>>> and use
>>>
>>> =LEFT(FormulaIs)="="
>>>
>>> or in a pedagogic manner
>>>
>>> =LEFT(FormulaIs,1)="="
>>>
>>> you can actually leave out 1 if you just want one character
>>>
>>> now click the format button and select format and click OK twice
>>>
>>> But, I cannot use this method, because I get a security warning for Excel
>>> 4.0 macros when I open the template.
>>>
>>>
>>> Is there any other way of checking if a cell contains formula in conditional
>>> formatting?
>>
>> Do you have to use Conditional formatting to do this? Not VBA?
>>

I dont that would help.. I need the full undo stack

"salgud" wrote:

> What about saving the file under a modified name so the user can open the
> saved file to replicate the undo? Of course, wouldn't work if you need the
> full stack.
>
>
> On Tue, 28 Jul 2009 20:53:58 -0700, Haritha Kolla wrote:
>
> > Salgud Thanks for the reply...
> > If user overwrites formula in the cell, the back ground should be changed to
> > different color. But, if we implement this in VBA code or VSTO on cell
> > change, the undo stack will be lost...and user will not be able to undo his
> > changes. That is the reason why I wanted to handle it using Conditional
> > Formatting..
> >
> > "salgud" wrote:
> >
> >> On Tue, 28 Jul 2009 02:15:01 -0700, Haritha Kolla wrote:
> >>
> >>> Cell background has to be changed, if the cell doesn't contain formula.
> >>>
> >>> I have tried implementing it in following ways
> >>>
> >>> I have tried changing the cell back ground using Conditional formatting with
> >>> User Defined function(to check if cell has formula or value); but, adding
> >>> User Defined function degrades the performance of the template.
> >>> I have tried changing background by executing VSTO code on Range Change
> >>> event. But, undo stack is lost and user is unable to undo the changes
> >>> I also tried using Excel 4.0 macro..please see the implementation below..
> >>>
> >>> You can use Excel 4 macro in a defined name formula to check if cell has a
> >>> formula, using you example
> >>> do insert>name>define, in the source box put
> >>>
> >>> =GET.CELL(6,Sheet1!$A1)
> >>>
> >>> and in the name box type in something descriptive like
> >>>
> >>> FormulaIs
> >>>
> >>> click OK
> >>>
> >>>
> >>> Select A1:A3
> >>> with A1 as the active cell, do format>conditional formatting, select formula
> >>> is
> >>> and use
> >>>
> >>> =LEFT(FormulaIs)="="
> >>>
> >>> or in a pedagogic manner
> >>>
> >>> =LEFT(FormulaIs,1)="="
> >>>
> >>> you can actually leave out 1 if you just want one character
> >>>
> >>> now click the format button and select format and click OK twice
> >>>
> >>> But, I cannot use this method, because I get a security warning for Excel
> >>> 4.0 macros when I open the template.
> >>>
> >>>
> >>> Is there any other way of checking if a cell contains formula in conditional
> >>> formatting?
> >>
> >> Do you have to use Conditional formatting to do this? Not VBA?
> >>
>