how can i test whether cell contains keyed constant or a formulae

[Ruthki]

I would like to use conditional formatting to test whether a cell
contains text or a formulae and higlight all cells as they are
overtyped.

I have tried using Type() but doesnt recognise difference between sam a
number keyed in and a formulae beginning =if(. . . .

Any ideas?

 

[anilsolipuram]

You have to write a userdefined function which can determine whether a
cell has formula or not

below is that funtion which determines whether a cell has formula or
not, it takes the cell address as the parameter

Function check_formula(r As Range)
If r.Formula = "" Then
check_formula = False
Else
check_formula = True
End If

End Function

You have to paste the the above function in vba module.

to use it in any cell type , =check_formula(b3) ' this will find
whether cell b3 has formula or not , if formula is there it will return
true, else false

 

[Dave Peterson]

I bet you meant something more like:

Function check_formula(r As Range)
If r.hasFormula Then
check_formula = true
Else
check_formula = false
End If

End Function

 

[Ruthki]

Thanks Anil - It worked and I learnt a bit more

 

[Ruthki]

Dave
Will this differentiate between a keyed constant and a formula - the
difficulty I have encountered is that the keyed constant is recognised
as a formula and therefore result will be true for both ?

 

[kk]

Try http://www.j-walk.com/ss/excel/usertips/tip045.htm . It works for me.

 

[Dave Peterson]

r.hasFormula

will be true if the cell r has a formula. If it has text that starts with an
equal sign (but still text), it will return false.