How to determine formula or simple value in a cell?

  • Thread starter Thread starter Tom Hayakawa
  • Start date Start date
T

Tom Hayakawa

I am trying to set a conditional format to change the
background of a cell based on whether it's a formula or
just a simple value. This makes it easier to re-set when
we clean the spreadsheet off to re-use. I tried the
FORMULATEXT funtion from Morefunc, but conditional
formating won't accept it. Can anyone think of a way I
might accomplish this? Thanks in advnace.
 
This makes it easier to re-set when
we clean the spreadsheet off to re-use.
Click to expand...

Have you considered (having selected a range) using the "Go To (F5) Special"
options (constants or formulas)? Or try setting up a template to begin with.

HTH,
Andy
 
One way:

1. Press Ctrl+F3 and define a name "isformula" as:

=LEFT(GET.FORMULA(INDIRECT("rc",FALSE)))="="

2. Select your cells.
3. Format > Conditional Formatting > Formula Is:

=isformula

and format as the pattern color as desired.

5. Read this warning about using XLM with XL versions
earlier than 2002:

http://makeashorterlink.com/?F3A022A44

HTH
Jason
Atlanta, GA
 
-----Original Message-----
I am trying to set a conditional format to change the
background of a cell based on whether it's a formula or
just a simple value. This makes it easier to re-set when
we clean the spreadsheet off to re-use. I tried the
FORMULATEXT funtion from Morefunc, but conditional
formating won't accept it. Can anyone think of a way I
might accomplish this? Thanks in advnace.
.
Click to expand...

This wouldn't color the cells as you're trying but would
be a very quick, neat way of cleaning that file once you
had it set up.

First, go to Tools/Options/View and check Formulas....all
formulas would be shown and easy to see.

Then record a macro assigning it to a Ctrl key or a
button. Using the Shift/Ctrl key as appropriate selecting
(highlighting) all non-formula cells. Hit
Edit/Clear/Contents. Close the recorder.

Make sure you have a backup copy of your sheet as you test
your results but this would be a one click clearing of
your sheet, once that macro is recorded.

A template, as suggested is probably the best.

It works......HTH

Don
 
Jason.Morin wrote...
One way:

1. Press Ctrl+F3 and define a name "isformula" as:

=LEFT(GET.FORMULA(INDIRECT("rc",FALSE)))="="
Click to expand...
...

This is potentially a bug. It'd return TRUE if the cell contained
text constant beginning with =, e.g., simplistic double underlinin
such as

=================

Safer, it you're going to use XLM, to check that the cell really doe
contain a formula

=GET.CELL(INDIRECT("RC",0),48)

That said, it's better to use VBA for this. VBA udfs can be used i
Conditional Formatting formula criteria.

Function HasFormula(Optional r As Range) As Boolean
If r Is Nothing And TypeOf Application.Caller Is Range Then
HasFormula = Application.Caller.Cells(1).HasFormula
Else
HasFormula = r.Cells(1).HasFormula
End If
End Function

While it's possible to write a VBA udf wrappers for functions i
MOREFUNC.XLL using Evaluate to call those functions, there's no benefi
in this case
 
Thanks to everyone who replied. I was trying to avoid
having to record a macro, as this spreadsheet was going to
be used by very novice folks and I didn't want to unduly
burden them with instructions beyond "look for the colored
cells". It appears that might not be possible, so I guess
I'll investigate setting up the simplest macro possible.
Thanks again for all the ideas.
 
Back
Top