change cell colour for all cells in sheet containing formula's

[Robert1311]

Hi there, is there a way to highlight all cells with formula's in a sheet automatically ?
I know it is possible to show the formula in cells, but I still want to see the result and not the formula!
When the cells get a specific colour I know a formula is involved.
Hopefully someone can help me out!

Regards,
Robert

[batchy]

Hi Robert,

There are 2 stages to displaying the view you need.

#1.
-Open the worksheet containing those formulas and press Alt + F11
-Go to Insert | Module.
-Enter the following function:

Please Login or Register  to view this content.

#2.
-Press Alt + Q.
-Go to Format | Conditional Formatting. In Excel 2007, click Conditional Formatting on the Home tab.
-Choose Formula Is from the Condition 1 drop-down list. In Excel 2007, click New Rule and then click Use A Formula To Determine Which Cells To Format.
-Enter the following formula:

Please Login or Register  to view this content.

-Click the Format button.
-Click on the Patterns tab, select the colour you desire, and click OK. (In Excel 2007, go to the Fill tab, choose the colour under Background color and click OK.
-Click OK.


Thanks

Batchy

[buran]

You can use conditional formatting for this. Create rule based on formula, that applies to all cells in the sheet(some range). Use IsFormula in the rule formula

[Robert1311]

Hi Batchy, thanks a lot , works fine!

Rgds Robert

[batchy]

No worries, if you could add reputation points & mark the thread as solved that would be great.