No error but unfunctioning format condition in vba

[mailmaster]

I have this two functions in a module:

Function IsNotFormula(rRango As Range) As Boolean
IsNotFormula = True
If rRango.HasFormula = True Then
IsNotFormula = False
End If
End Function

Sub marcaSinformulas(rRango As Range)
Dim miCondicion As FormatCondition
Dim sPrimeraCelda As String
Dim sCondicion As String
Dim rCelda As Range
rRango.FormatConditions.Delete
For Each rCelda In rRango.Cells
sPrimeraCelda = rCelda.Cells(1, 1).Address
sPrimeraCelda = Replace(sPrimeraCelda, "$", "")
sCondicion = "=IsNotFormula(" & sPrimeraCelda & ")"
On Error Resume Next
rCelda.FormatConditions.Add Type:=xlExpression, Formula1:=sCondicion
rCelda.FormatConditions(1).Interior.ColorIndex = 36
Next
End Sub

And when I try to trace it, the execution is missed in the line:

rCelda.FormatConditions.Add Type:=xlExpression, Formula1:=sCondicion

It doesn't matter if I put the line

On error resume next

or not.

But when I see the condicional format of any cell of the range the condition is correct but the format is not set.

I really don't know what's going on.
Any help??
TIA

 

[Greg Truby]

Yeah, I'd suspect the Text function was the problem there. It's always risky using that in a UDF, since it's so closely tied to the screen display.

Rory - can you elaborate? I was unaware that the text property of a range object would vary due to any screen/display property.

 

[RoryA]

Try making a column too narrow for the value to display and see what happens... There's also mention on Charles' site that it doesn't always work in UDFs. I'll find the link.

 

[Greg Truby]

Hmmm, I tried that in 2003 & 2007 and hopping into the active window & typing ? activecell.text is returning the full text even if the column width is low (.5) [I'm putting text into the cell to the right to prevent the "long" text from showing.] and even if the entire column is hidden. You're very rarely wrong about this type of thing... Perhaps this was a bug in an older version? Or perhaps my testing not replicating the correct conditions?

AHA! The .Text property still works fine on narrow columns that contain TEXT. Where it fails is if the cell contains a numeric or a date where the formatted output exceeds the width. Indeed, one gets the "#######" if the column is too narrow for the formatted value, but not so narrow that a single "#" cannot be displayed.

Thanks a bunch, Rory. Learned sumpin' new today!