software

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

You need to create a UDF and use that

Function IsFormula(rng As Range)
If rng.Count gt; 1 Then
IsFormula = CvErr(xlErrRef)
Else
IsFormula = rng.HasFormula
End If
End Function

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;Gerritquot; lt;Gerrit @discussions.microsoft.comgt; wrote in message
...
gt; I want to set conditional format so that it show when a cell contains a
gt; formula and when a cell contains a value...
gt;
gt; This would be most valuable to use when user needs to input values..
Gerrit, oddly enough, i am trying to do the same today, just about to post
question wheni read yours. We have tried isref amp; isnumber but that doesnt
work, have you had any luck yet?

Steve

quot;Gerritquot; wrote:

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

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

Steve

quot;Bob Phillipsquot; wrote:

gt; You need to create a UDF and use that
gt;
gt; Function IsFormula(rng As Range)
gt; If rng.Count gt; 1 Then
gt; IsFormula = CvErr(xlErrRef)
gt; Else
gt; IsFormula = rng.HasFormula
gt; End If
gt; End Function
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from email address if mailing direct)
gt;
gt; quot;Gerritquot; lt;Gerrit @discussions.microsoft.comgt; wrote in message
gt; ...
gt; gt; I want to set conditional format so that it show when a cell contains a
gt; gt; formula and when a cell contains a value...
gt; gt;
gt; gt; This would be most valuable to use when user needs to input values..
gt;
gt;
gt;

Great, maybe a 2-for-1 lt;vbggt;

Bob

quot;Stevequot; gt; wrote in message
...
gt; Bob, not my initial query but thanks, spot on.
gt;
gt; Steve
gt;
gt; quot;Bob Phillipsquot; wrote:
gt;
gt; gt; You need to create a UDF and use that
gt; gt;
gt; gt; Function IsFormula(rng As Range)
gt; gt; If rng.Count gt; 1 Then
gt; gt; IsFormula = CvErr(xlErrRef)
gt; gt; Else
gt; gt; IsFormula = rng.HasFormula
gt; gt; End If
gt; gt; End Function
gt; gt;
gt; gt; --
gt; gt; HTH
gt; gt;
gt; gt; Bob Phillips
gt; gt;
gt; gt; (remove nothere from email address if mailing direct)
gt; gt;
gt; gt; quot;Gerritquot; lt;Gerrit @discussions.microsoft.comgt; wrote in message
gt; gt; ...
gt; gt; gt; I want to set conditional format so that it show when a cell contains
a
gt; gt; gt; formula and when a cell contains a value...
gt; gt; gt;
gt; gt; gt; This would be most valuable to use when user needs to input values..
gt; gt;
gt; gt;
gt; gt;

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: www.excelforum.com/member.php...foamp;userid=8991
View this thread: www.excelforum.com/showthread...hreadid=512585Yes 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)

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

Gerritquot;Bob Phillipsquot; wrote:

gt; Yes you need the UDF as it is that that recognises a formula.
gt;
gt; Add the UDF to your workbook.
gt;
gt; Select your cells, I'm assuming that you start at A1
gt; In CF change Condition 1 To Formula Is
gt; Add =IsFormula(A1)
gt; Set your format
gt; OK
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from email address if mailing direct)
gt;
gt; quot;mworth01quot; gt; wrote in
gt; message ...
gt; gt;
gt; gt; I'm still a little confused as to how you then apply your UDF to a
gt; gt; conditional format. The conditional format that I'm interested in
gt; gt; applies only when a cell contains a formula; I'm not worried about
gt; gt; whether it's a number or not. Do you still need a UDF since you only
gt; gt; have one condition now? I'm new to conditional formatting, so thanks
gt; gt; in advance for any help.
gt; gt;
gt; gt;
gt; gt; --
gt; gt; mworth01
gt; gt; ------------------------------------------------------------------------
gt; gt; mworth01's Profile:
gt; www.excelforum.com/member.php...foamp;userid=8991
gt; gt; View this thread: www.excelforum.com/showthread...hreadid=512585
gt; gt;
gt;
gt;
gt;

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)

quot;Gerritquot; gt; wrote in message
...
gt; Bob you opened a new world for me, Thanks a million!!
gt;
gt; The UDF and the conditional Format works excellant on my computer (excell
gt; 2003).. For some strange reason I can't get it going on other computers
also
gt; 2003... Went through the same drills (creating a UDF) but no luck...
gt;
gt; Gerrit
gt;
gt;
gt; quot;Bob Phillipsquot; wrote:
gt;
gt; gt; Yes you need the UDF as it is that that recognises a formula.
gt; gt;
gt; gt; Add the UDF to your workbook.
gt; gt;
gt; gt; Select your cells, I'm assuming that you start at A1
gt; gt; In CF change Condition 1 To Formula Is
gt; gt; Add =IsFormula(A1)
gt; gt; Set your format
gt; gt; OK
gt; gt;
gt; gt; --
gt; gt; HTH
gt; gt;
gt; gt; Bob Phillips
gt; gt;
gt; gt; (remove nothere from email address if mailing direct)
gt; gt;
gt; gt; quot;mworth01quot; gt; wrote
in
gt; gt; message ...
gt; gt; gt;
gt; gt; gt; I'm still a little confused as to how you then apply your UDF to a
gt; gt; gt; conditional format. The conditional format that I'm interested in
gt; gt; gt; applies only when a cell contains a formula; I'm not worried about
gt; gt; gt; whether it's a number or not. Do you still need a UDF since you only
gt; gt; gt; have one condition now? I'm new to conditional formatting, so thanks
gt; gt; gt; in advance for any help.
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt; mworth01
gt; gt;
gt; ------------------------------------------------------------------------
gt; gt; gt; mworth01's Profile:
gt; gt; www.excelforum.com/member.php...foamp;userid=8991
gt; gt; gt; View this thread:
www.excelforum.com/showthread...hreadid=512585
gt; gt; gt;
gt; gt;
gt; gt;
gt; gt;
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)lt;gt;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.

quot;Bob Phillipsquot; wrote:

gt; That's good Gerrit.
gt;
gt; When you use CF you must be very careful which cell is active when you
gt; define it, as most of the formulae are relative, so can easily get offset by
gt; your current position. As I mentioned, the cell reference that you use when
gt; setting up the CF should be the active cell, either a single active cell, or
gt; the active cell in a selection.
gt;
gt; Oddly enough, I just re-did it, and on my first workbook it failed. Looking
gt; at the function list, it said there was a function Book1!Module1IsFormula.
gt; As I was in Book1, it should have just said IsFormula. But I haven't quite
gt; twigged why yet, but it failed in CF and in the worksheet. Creating a new
gt; workbook and doing it again, it worked fine. Odd!
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from email address if mailing direct)
gt;
gt; quot;Gerritquot; gt; wrote in message
gt; ...
gt; gt; Bob you opened a new world for me, Thanks a million!!
gt; gt;
gt; gt; The UDF and the conditional Format works excellant on my computer (excell
gt; gt; 2003).. For some strange reason I can't get it going on other computers
gt; also
gt; gt; 2003... Went through the same drills (creating a UDF) but no luck...
gt; gt;
gt; gt; Gerrit
gt; gt;
gt; gt;
gt; gt; quot;Bob Phillipsquot; wrote:
gt; gt;
gt; gt; gt; Yes you need the UDF as it is that that recognises a formula.
gt; gt; gt;
gt; gt; gt; Add the UDF to your workbook.
gt; gt; gt;
gt; gt; gt; Select your cells, I'm assuming that you start at A1
gt; gt; gt; In CF change Condition 1 To Formula Is
gt; gt; gt; Add =IsFormula(A1)
gt; gt; gt; Set your format
gt; gt; gt; OK
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt; HTH
gt; gt; gt;
gt; gt; gt; Bob Phillips
gt; gt; gt;
gt; gt; gt; (remove nothere from email address if mailing direct)
gt; gt; gt;
gt; gt; gt; quot;mworth01quot; gt; wrote
gt; in
gt; gt; gt; message ...
gt; gt; gt; gt;
gt; gt; gt; gt; I'm still a little confused as to how you then apply your UDF to a
gt; gt; gt; gt; conditional format. The conditional format that I'm interested in
gt; gt; gt; gt; applies only when a cell contains a formula; I'm not worried about
gt; gt; gt; gt; whether it's a number or not. Do you still need a UDF since you only
gt; gt; gt; gt; have one condition now? I'm new to conditional formatting, so thanks
gt; gt; gt; gt; in advance for any help.
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; --
gt; gt; gt; gt; mworth01
gt; gt; gt;
gt; gt; ------------------------------------------------------------------------
gt; gt; gt; gt; mworth01's Profile:
gt; gt; gt; www.excelforum.com/member.php...foamp;userid=8991
gt; gt; gt; gt; View this thread:
gt; www.excelforum.com/showthread...hreadid=512585
gt; gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt;
gt;
gt;