Set conditional format to show when a sell contain a formula

[Gerrit]

I want to set conditional format so that it show when a cell contains a
formula and when a cell contains a value...

This would be most valuable to use when user needs to input values..

[Bob Phillips]

You need to create a UDF and use that

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)

"Gerrit" <Gerrit @discussions.microsoft.com> wrote in message
news:4BDE9FA5-DD44-43C8-930D-B8B9B565EED5@microsoft.com...
> I want to set conditional format so that it show when a cell contains a
> formula and when a cell contains a value...
>
> This would be most valuable to use when user needs to input values..

[Steve]

Bob, not my initial query but thanks, spot on.

Steve

"Bob Phillips" wrote:

> You need to create a UDF and use that
>
> 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)
>
> "Gerrit" <Gerrit @discussions.microsoft.com> wrote in message
> news:4BDE9FA5-DD44-43C8-930D-B8B9B565EED5@microsoft.com...
> > I want to set conditional format so that it show when a cell contains a
> > formula and when a cell contains a value...
> >
> > This would be most valuable to use when user needs to input values..
>
>
>

[Bob Phillips]

Great, maybe a 2-for-1 <vbg>

Bob

"Steve" <Steve@discussions.microsoft.com> wrote in message
news:5BDA5965-04F4-45FF-885B-48610F54008F@microsoft.com...
> Bob, not my initial query but thanks, spot on.
>
> Steve
>
> "Bob Phillips" wrote:
>
> > You need to create a UDF and use that
> >
> > 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)
> >
> > "Gerrit" <Gerrit @discussions.microsoft.com> wrote in message
> > news:4BDE9FA5-DD44-43C8-930D-B8B9B565EED5@microsoft.com...
> > > I want to set conditional format so that it show when a cell contains
a
> > > formula and when a cell contains a value...
> > >
> > > This would be most valuable to use when user needs to input values..
> >
> >
> >

[mworth01]

I'm still a little confused as to how you then apply your UDF to a conditional format. The conditional format that I'm interested in applies only when a cell contains a formula; I'm not worried about whether it's a number or not. Do you still need a UDF since you only have one condition now? I'm new to conditional formatting, so thanks in advance for any help.

[Bob Phillips]

Yes you need the UDF as it is that that recognises a formula.

Add the UDF to your workbook.

Select your cells, I'm assuming that you start at A1
In CF change Condition 1 To Formula Is
Add =IsFormula(A1)
Set your format
OK

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"mworth01" <mworth01.23bz4b_1140107703.0857@excelforum-nospam.com> wrote in
message news:mworth01.23bz4b_1140107703.0857@excelforum-nospam.com...
>
> I'm still a little confused as to how you then apply your UDF to a
> conditional format. The conditional format that I'm interested in
> applies only when a cell contains a formula; I'm not worried about
> whether it's a number or not. Do you still need a UDF since you only
> have one condition now? I'm new to conditional formatting, so thanks
> in advance for any help.
>
>
> --
> mworth01
> ------------------------------------------------------------------------
> mworth01's Profile:
http://www.excelforum.com/member.php...fo&userid=8991
> View this thread: http://www.excelforum.com/showthread...hreadid=512585
>

[Steve]

Gerrit, oddly enough, i am trying to do the same today, just about to post
question wheni read yours. We have tried isref & isnumber but that doesnt
work, have you had any luck yet?

Steve

"Gerrit" wrote:

> I want to set conditional format so that it show when a cell contains a
> formula and when a cell contains a value...
>
> This would be most valuable to use when user needs to input values..