detecting whether a cell contains value or formula

  • Thread starter Thread starter gene
  • Start date Start date
G

gene

I need to be able to have a cell report (e.g.,
conditional formatting) whether its contents are a
constant value or is a calculation/formula.
Without stepping through each cell! ;-)

I can't find a function that will report this fundamental
difference ... nor an option (other than Tools->Options-
Formulas).
Click to expand...

Ideally, a simple toggle would let me see (didn't 1-2-3
do that?)

thanks
 
Gene

I don't think this is what you want but you can toggle formula view and
value view by using Ctrl+` (UK Keyboard)

This key differs on regional keyboards, but it is always the key to the left
of the 1 on the top row of keys

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
 
I need to be able to have a cell report (e.g.,
conditional formatting) whether its contents are a
constant value or is a calculation/formula.
Without stepping through each cell! ;-)

I can't find a function that will report this fundamental
difference ... nor an option (other than Tools->Options-

Ideally, a simple toggle would let me see (didn't 1-2-3
do that?)
Click to expand...

123 provides 'formula markers' - little dots that appear in the lower left
corners of cells that contain formulas. They're not all that visually striking.
Excel 97 at least doesn't provide this.

But Excel provides something better. Select an entire worksheet by clicking on
the box in the worksheet frame just above the row numbers and just to the left
of the column letters, press [F5] or issue the Edit > Goto menu command, click
on Special..., select Formulas and click OK. This will restrict the selection to
all cells in the active worksheet that contain formulas. This could be
restricted even further by selecting which sort of results you want - Numbers,
Text, Logicals (stupid terminology), or Errors.
 
Gene,
The following is a string from "Conditional Formatting when Overtyyping a Function" from 12/2/03 in this newsgroup. Hopefully it is what you are looking for:


------------------------------------------------------------

Is there a way to use conditional formatting to shade a cell background if a
user overtypes a formula that already exists in a cell ?

In case I'm not being clear, I have to enter a figure in A1, calculation
results are then shown in B1 and C1. However if those calculations do not
match other information already given to me then I am manually overtyping
the field and then clicking on the fill colour icon to notify myself that it
is an overwrite. Can this be done automatically ? if not with Conditional
formatting then could anyone point me in the direction of a UDF for this
sort of thing.

Many thanks

Harry

----------------------------------------------------------------------

Harry,
I think I found a way to do this. My example has numbers in A1 and B1 with the formula =A1*B1 in cell C1.

The condition is "Formula is" =A1*B1<>C1

Replace A1*B1 with your formula, and C1 with the reference of the cell containing your formula. This way if the calculated value of the formula does not equal the value in the cell, the conditional format will be applied.

Good Luck,
Mark Graesser
(e-mail address removed)


----- gene wrote: -----

I need to be able to have a cell report (e.g.,
conditional formatting) whether its contents are a
constant value or is a calculation/formula.
Without stepping through each cell! ;-)

I can't find a function that will report this fundamental
difference ... nor an option (other than Tools->Options-
Formulas).
Click to expand...

Ideally, a simple toggle would let me see (didn't 1-2-3
do that?)

thanks
 
Gene

Copy/paste this UDF to a general module in your workbook.

Function IsFormula(cell)
Application.Volatile
IsFormula = cell.HasFormula
End Function

To insert a general module hit ALT + F11 to go to Visual Basic Editor.
Find you wotkbook/project and right-click and Insert>Module.
Paste the UDF in there.
ALT + Q to back to Excel.

On your worksheet select a bunch of cells and.......

Using CF you will enter Formula is: =IsFormula(activecell) where activecell is
A1 or E4 or whatever.

Pick a pattern from Format.

This CF will be in effect for all cells you originally selected.

NOTE: UDF must be in the workbook. Cannot be placed in Personal.xls or an
Add-in.

This is a limitation of CF. "You may not use references to other worksheets
or workbooks."



Gord Dibben Excel MVP
 
Back
Top