how can i tell if a cell contains a formula?

R

R of Steinke

How can I tell if a cell contains a formula versus a number or text? (apart
from looking at it). i want to create a conditional format for cells so that
if users enter a formula (or reference to another cell - basically anything
starting with "=" , it appears in a different format.
 
B

Bob Phillips

Use a UDF

Function IsFormula(rng As Range)
If rng.Count > 1 Then
IsFormula = CVErr(xlErrRef)
Else
IsFormula = rng.HasFormula
End If
End Function


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
E

edcosoft

I tried using the HasFormula in the link and it doesn't work with my
Excel 2003. No referrence to it in "Help" and the function doesn't
work. I need the same function. Got any suggestions? ed
 
D

Dave Peterson

This user defined function is not built into excel. It's a function that is
created by the user.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Short course:

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Function HasFormula(cell)
HasFormula = cell.HasFormula
End Function

(From David McRitchie's site)

Now go back to excel.
Into a test cell and type:
=hasformula(a1)
 
E

edcosoft

Fantastic!!! Thank you. I used it to conditional format a cell and
works perfectly. Now to learn of the other user defined functions
available in the link you supplied. ed
 
D

Dave Peterson

And one of the neat things about these UDFs is that you can make your own--as
complex as you need.
 
Top