There's no easy way using formula to get Excel to tell you if a cell contains a formula or a literal value, you'd need to use a little bit of VB script to give you a User Defined Function (UDF) ...
Function IsFormula(rngTarget As Range) As Boolean
Dim vCompareValue As Variant
Dim rngCellLoop As Variant
Dim bTempStat As Boolean
bTempStat = False
For Each rngCellLoop In rngTarget.Cells
With rngCellLoop
vCompareValue = .Formula
If IsNumeric(vCompareValue) Then
vCompareValue = Val(vCompareValue)
End If
If vCompareValue <> .Value Then
bTempStat = True
End If
End With
Next rngCellLoop
IsFormula = bTempStat
End Function
This allows you to use IsFormula as a worksheet function, with it returning TRUE if any of the cells in the range it is passed contain a formula.
Bookmarks