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

 

[SamTYler]

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

Dim FCCount As Long
FCCount = rCelda.FormatConditions.Count
If FCCount < 3 Then
rCelda.FormatConditions(FCCount + 1).Add Type:=xlExpression, Formula1:=sCondicion
rCelda.FormatConditions(FCCount + 1).Interior.ColorIndex = 36
Else
MsgBox("Celda " & rCelda.Address & "Tiene que mucho conditiones!")
Exit Sub
...
End Sub

Sorry for my bad use of your laguage.

 

[mailmaster]

Also I tried this way with the same result:

Sub marcaSinformulas(rRango As Range)
Dim miCondicion As FormatCondition
Dim sPrimeraCelda As String
Dim sCondicion As String

rRango.FormatConditions.Delete
sPrimeraCelda = rRango.Cells(1, 1).Address
sPrimeraCelda = Replace(sPrimeraCelda, "$", "")
sCondicion = "=IsNotFormula(" & sPrimeraCelda & ")"
On Error Resume Next
rRango.FormatConditions.Add Type:=xlExpression, Formula1:=sCondicion
rRango.FormatConditions(1).Interior.ColorIndex = 36
​

End Sub

And in this way I think it is clear there aren't exceed of condition, because the sub clear all at entrance, so i guess there is only one (this one).

 

[SamTYler]

AFAIK, there is no "isNotFormula"

This will format cell with formula. Change Operator to xlEqual to format cell without formula. idunnowhy

XlFormatConditionType:= xlCellValue
xXFormatConditionOperator:= xlnotEqual
Formula1:= "=*"
[COLOR=green]'or maybe[/COLOR]
Formula1:= "="=*""

 

[Norie]

mailmaster

What are you trying to do with this code?

Do you have calculations set to automatic?

If you don't then the formula and function might not be getting calculated.

Also since you are using a UDF you might want to add Application.Volatile to it's code to make sure it is executed.

Sam

There is a function called 'isNotFormula', if you create one yourself - which is what the OP's original code seems to do.

 

[mailmaster]

AFAIK, there is no "isNotFormula"
...

XlFormatConditionType:= xlCellValue
...

I need XlFormatConditionType:=xlExpression

And "isNotFormula" is my boolean function as I said in the first post:

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

This will format cell with formula. Change Operator to xlEqual to format cell without formula. idunnowhy

XlFormatConditionType:= xlCellValue
xXFormatConditionOperator:= xlnotEqual
Formula1:= "=*"
'or maybe
Formula1:= "="=*""
</pre>

idunnowhy: if is "=" & "*" (anything) wil be formula...

 

[RoryA]

Have you tried selecting the cells in the sub before you add the conditional formatting?

 

[SamTYler]

if is "=" & "*" (anything) wil be formula...

My bad. You're correct. So sorry

My last try for you

sCondicion = "=IsNotFormula(rRango)"

Function IsnotFormula wants a Range Type parameter. sPrimeraCelda is a String Type.

 

[mailmaster]

Yes, tried. Same result.

 

[mailmaster]

sCondicion = "=IsNotFormula(rRango)"

Function IsnotFormula wants a Range Type parameter. sPrimeraCelda is a String Type.

but the condition must be a string type: sCondicion = "=IsNotFormula(rRango)" in the condition is transcripted literally to the conditional format and so it is not valid...

Thank you for your tries.