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 insertnamedefine, 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 formatconditional 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?