isformula

  • Thread starter Thread starter anneliise
  • Start date Start date
A

anneliise

Hi,

any ideas how to detect if a formula is used in a cell?
Like ISTEXT finds if the cell value is text, i need to find out if th
cell contains formula.
I want to use it with conditional formatting. For example if a cel
contains formula, it should appear red.

Thanks,
Anneliis
 
Annelise,

One way is to setup your own UDF, like so

Function IsFormula(rng As Range) As Boolean
If rng.Count > 1 Then
IsFormula = CVErr(xlErrValue)
Else
IsFormula = rng.HasFormula
End If
End Function

test like so

=IsFormula(A1)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
any ideas how to detect if a formula is used in a cell?
Like ISTEXT finds if the cell value is text, i need to find out if the
cell contains formula.
I want to use it with conditional formatting. For example if a cell
contains formula, it should appear red.
Click to expand...

A frequently asked and answered question.

http://www.google.com/[email protected]

Unless your cell contents would change frequently between formulas and
constants, you may be better off selecting entire worksheets by clicking in the
box in the sheet frame (the one to the left of column letters and above row
numbers), then Edit > Goto, click Special, then select Formulas and click OK.
This will select all cells currently containing formulas. You could then format
them all in a batch.
 
...
...
Function IsFormula(rng As Range) As Boolean
If rng.Count > 1 Then
IsFormula = CVErr(xlErrValue)
Else
IsFormula = rng.HasFormula
End If
End Function
Click to expand...
...

Good to distinguish between single cell and multiple cell ranges, bad to choke
on the latter.


Function IsFormula(r As Range) As Variant
Dim i As Long, j As Long, rv As Variant

Set r = r.Areas(1) 'since there's no way to handle multiple area ranges

rv = r.Value

If IsArray(rv) Then
For i = 1 To r.Rows.Count
For j = 1 To r.Columns.Count
rv(i, j) = r.Cells(i, j).HasFormula
Next j
Next i

Else
rv = r.HasFormula

End If

IsFormula = rv
End Function
 
Back
Top