how can i tell if a cell contains a formula?

[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.

[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)

"R of Steinke" <RofSteinke@discussions.microsoft.com> wrote in message
news:FE954019-4515-42C7-B98C-9D23C9251BFF@microsoft.com...
> 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.

[JE McGimpsey]

See

http://www.mvps.org/dmcritchie/excel...htm#HasFormula


In article <FE954019-4515-42C7-B98C-9D23C9251BFF@microsoft.com>,
R of Steinke <RofSteinke@discussions.microsoft.com> wrote:

> 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.

[edcosoft@sbcglobal.net]

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

[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)

edcosoft@sbcglobal.net wrote:
>
> 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

--

Dave Peterson

[edcosoft@sbcglobal.net]

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

[Dave Peterson]

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



edcosoft@sbcglobal.net wrote:
>
> 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

--

Dave Peterson