Apply Conditional formatting if cell contains formula

[Haritha Kolla]

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?

 

[Atishoo]

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]

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

 

[salgud]

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?

 

[Haritha Kolla]

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]

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.

 

[Haritha Kolla]

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