How to identify cells containing formulae with no external references

[amaroo460]

<!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--> In Excel 2003 I want to apply conditional formats to entire worksheets to highlight with colour:
a) cells with constant numerical values, including formulae with no external references (e.g. a volume entered as a product of 3 numbers - I do this so I can see where the volume figure came from);
b) cells containing formulae that don't reduce to constant value (i.e. with references to other cells).

I'm 90% there using the function IsNumber and a function IsFormula created in Virtual Basic:

Function IsFormula(c)
IsFormula = c.HasFormula
End Function
This identifies formula cells and other numeric cells. My problem is how do I identify cells with constant value formulae to colour them as a) rather than b)?

<!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} </style> <![endif]-->

 

[krausr79]

Not the same as coloring things, but have you considered messing around with the 'trace precedents' or 'trace dependents'?

 

[Marcelo Branco]

If you are using A1 reference-style in your worksheet (not R1C1 reference-style) maybe this

Function IsFormula(c) As Boolean
    Dim inputFormula As String, convertedFormula As String
    
    If c.HasFormula Then
        inputFormula = c.Formula
        convertedFormula = Application.ConvertFormula(Formula:=inputFormula, fromReferenceStyle:=xlA1, _
            toReferenceStyle:=xlR1C1)
        IsFormula = inputFormula <> convertedFormula
    End If
 
End Function

M.

 

[amaroo460]

Hi Marcelo - so simple! I've not done much coding since writing Fortran and Algol in the '70s and '80s so I'm pretty rusty! Your help much appreciated. Peter

 

[Marcelo Branco]

Hi Marcelo - so simple! I've not done much coding since writing Fortran and Algol in the '70s and '80s so I'm pretty rusty! Your help much appreciated. Peter

Hi Peter,

Fortran, yes, i remember!!! (looong time ago...)

Thanks for the feedback.

M.