MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Conditional Format for cells containg a formula


Posted by Peter Mullin on June 27, 2001 3:58 AM

I am trying to conditionally format cells that contain a formula; does anybody know of a simple way to do this interactively as excel functions all seem to act upon the result of the formula rather than the formula.


Posted by Connie on June 27, 2001 6:00 AM

Can you add more detail to the problem?

Peter,
Can you provide a bit more information for what you're trying to accomplish?

Posted by Peter Mullin on June 27, 2001 6:39 AM

More detail to the problem

To put it simply I have been asked to colour any cell that contains a formula grey whereas those that are number entries (or anything else) are to remain white.

As the formulae do not occur in any set pattern and the spreadsheet is large individual cell shading whilst not being difficult will be an ongoing time consuming task.

It might be that conditional formatting is not the way forward and that I should employ a macro; any advice would be therefore appreciated.

Posted by Aladin Akyurek on June 27, 2001 7:01 AM

Re: More detail to the problem

Peter

You need a way of checking whether a cel contains a formula or not. The following describes such a so-called user-defined function. Once this UDF in your workbook, the reat easy.

Activate a cell of interest (say A1), activate Format|Conditional Formatting, and on Settings tab choose "Formula is". Enter as a formula

=IsFormula(A1)

then select a format.

Aladin

PS. Here is the piece:

Determining Whether a Cell Contains a Function

"Excel has a variety of information functions, such as ISBLANK() and ISERROR(), for testing the type of entry in a cell. However, Excel doesn't have a function for determining whether an entry is a formula. To create a simple user-defined function for that purpose, first press [Alt][F11] to display the Visual Basic Editor. Then, issue the Insert/Module command. In the resulting code window, create the procedure

Function IsFormula(c)
IsFormula = c.HasFormula
End Function

Finally, return to Excel by pressing [Alt][F11]. Now, make an entry in cell A1. After doing so, enter the user-defined function =ISFORMULA(A1) in any other cell to determine whether cell A1 contains a formula. If cell A1 does contain a formula, the function will return TRUE; otherwise, it will return FALSE.

Note that when you enter a user-defined function in a workbook other than the one that contains it, you must include the name of the workbook. For instance, if you create a user-defined function in the workbook Financial Forecast.xls, you'd enter the function as

='Financial Forecast.xls'!ISFORMULA(A1)

You can avoid syntax errors when entering user-defined functions by using the Paste Function button on the Standard tool bar. "

==============