MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Conditional Format


Posted by Terry Bennett on August 30, 2001 12:15 AM

I want to apply a conditional format to a cell if it doesn't contain a formula. In particular, the cell should contain a formula using the SUM function [=sum(xx99..yy99)]. Is there a function that will identify this for me?


Posted by Aladin Akyurek on August 30, 2001 1:56 AM

If a cell in a given range does not contain a formula, that cell must have e.g., a specific color as background. Is this what you want?

Aladin

Posted by Terry Bennett on August 30, 2001 5:51 PM


:Terry

Posted by Aladin Akyurek on August 30, 2001 10:37 PM


> If a cell in a given range does not contain a formula, that cell must have e.g., a specific color as background. Is this what you want?

> Yes, that is what I am after

Terry

Activate the option Tools|Macro|Visual Basic Editor.
Activate the option Insert|Module.
Paste the following in the open space on the window with title having the "(code)" bit:

Function ContainsFormula(cell) As Boolean
ContainsFormula = cell.HasFormula
End Function

Activate the option File|Close and Return to Microsoft Excel.

Select the range, say A1:A10, where you want to apply conditional formatting.
Activate the option Format|Conditional Formatting.
Choose "Formula Is" for Condition 1 on the Conditional Formatting window.
Enter the following formula:

=NOT(CONTAINSFORMULA(A1))

Note. Enter just A1, not $A$1.

Activate the Format button.
Apply the formatting that you want.
Click OK.

Aladin

Posted by Terry Bennett on September 03, 2001 12:48 AM


Posted by Aladin Akyurek on September 03, 2001 5:57 AM

Terry,

An example workbook is underway to you.

Aladin

> If a cell in a given range does not contain a formula, that cell must have e.g., a specific color as background. Is this what you want? Activate the option Insert|Module. Paste the following in the open space on the window with title having the "(code)" bit: ContainsFormula = cell.HasFormula End Function Activate the option Format|Conditional Formatting. Choose "Formula Is" for Condition 1 on the Conditional Formatting window. Enter the following formula: Apply the formatting that you want. Click OK.