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" wrote in message
...
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" wrote
in
message ...
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