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]

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

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

[Gerrit]

Bob you opened a new world for me, Thanks a million!!

The UDF and the conditional Format works excellant on my computer (excell
2003).. For some strange reason I can't get it going on other computers also
2003... Went through the same drills (creating a UDF) but no luck...

Gerrit


"Bob Phillips" wrote:

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

[Bob Phillips]

That's good Gerrit.

When you use CF you must be very careful which cell is active when you
define it, as most of the formulae are relative, so can easily get offset by
your current position. As I mentioned, the cell reference that you use when
setting up the CF should be the active cell, either a single active cell, or
the active cell in a selection.

Oddly enough, I just re-did it, and on my first workbook it failed. Looking
at the function list, it said there was a function Book1!Module1IsFormula.
As I was in Book1, it should have just said IsFormula. But I haven't quite
twigged why yet, but it failed in CF and in the worksheet. Creating a new
workbook and doing it again, it worked fine. Odd!

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Gerrit" <Gerrit@discussions.microsoft.com> wrote in message
news:269DEBB1-343A-42D2-973C-874F3D8C99B0@microsoft.com...
> Bob you opened a new world for me, Thanks a million!!
>
> The UDF and the conditional Format works excellant on my computer (excell
> 2003).. For some strange reason I can't get it going on other computers
also
> 2003... Went through the same drills (creating a UDF) but no luck...
>
> Gerrit
>
>
> "Bob Phillips" wrote:
>
> > 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
> > >
> >
> >
> >

[kkf718]

I have gotten around this in a less sophisticated way in the past. In the
formula box, I have put in a formula using a condition that evaluates to TRUE
if the value of the cell equals the same result as the formula. This means
you need to repeat the formula from the cell in CF. Here is a simple example
I just wrote: =(SUM(B2:B4)<>B5), where the formula in B5 is =sum(B2:B4). It
highlights if the value is different from the answer the formula would give.
If someone overwrites the formula with a value that is the same as the
formula answer, it does not highlight, but I have not found this to be an
issue in practice.
I am not familiar with UDF's. I create big, complicated spreadsheets, and
don't use Macros because of their speed, but complicated formulas can be
cumbersome. Are UDF's fast? Where can I learn about them? If they are
fast, the UDF solution is much more elegant than mine.

"Bob Phillips" wrote:

> That's good Gerrit.
>
> When you use CF you must be very careful which cell is active when you
> define it, as most of the formulae are relative, so can easily get offset by
> your current position. As I mentioned, the cell reference that you use when
> setting up the CF should be the active cell, either a single active cell, or
> the active cell in a selection.
>
> Oddly enough, I just re-did it, and on my first workbook it failed. Looking
> at the function list, it said there was a function Book1!Module1IsFormula.
> As I was in Book1, it should have just said IsFormula. But I haven't quite
> twigged why yet, but it failed in CF and in the worksheet. Creating a new
> workbook and doing it again, it worked fine. Odd!
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "Gerrit" <Gerrit@discussions.microsoft.com> wrote in message
> news:269DEBB1-343A-42D2-973C-874F3D8C99B0@microsoft.com...
> > Bob you opened a new world for me, Thanks a million!!
> >
> > The UDF and the conditional Format works excellant on my computer (excell
> > 2003).. For some strange reason I can't get it going on other computers
> also
> > 2003... Went through the same drills (creating a UDF) but no luck...
> >
> > Gerrit
> >
> >
> > "Bob Phillips" wrote:
> >
> > > 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
> > > >
> > >
> > >
> > >
>
>
>