Conditional Formating Bug when using HasFormula UDF

  • Thread starter Thread starter Martin Brader
  • Start date Start date
M

Martin Brader

Something strange is happening in Excel 2003 SP2.
I am using this udf to Conditional Format a cell on Sheet2
Function cf_NotFormula(cell)
'based on http://www.mvps.org/dmcritchie/excel/formula.htm#HasFormula
cf_NotFormula = Not cell.HasFormula And Not IsEmpty(cell) _
And Not cell.Row = 1
End Function

When I go to Sheet1 cell A1 and type in "=" and then point to cell A2 on
Sheet2 (to hopefully create =Sheet2!A2 in cell A1 on Sheet1) and then press
ENTER something very strange happens. Sheet2 remains in view and a spurious
entry materialises in Cell A1 on Sheet2 (usually). I've got a small test
model available if you want try it for yourself.

I have narrowed the problem down to the .HasFormula property.
 
I have now come up with a workaround:

Public Function HasFormula(rngCell As Range) As Boolean
'NB rngCell.HasFormula causes problesm with Conditional formating.
On Error Resume Next
If Left$(rngCell.Formula, 1) = "=" Then
HasFormula = True
End If
End Function
 
Back
Top