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

 

[mailmaster]

What are you trying to do with this code?

Do you have calculations set to automatic?

I have a set of numbers imported from other sheets, sometimes I must to modify it manually, so I try to set a visual mark to see wich data is modified manually.

Yes of course calculations are set to automatic.

... add Application.Volatile to it's code to make sure it is executed.

I'm going to try this way... No luck, Excel is missing for a while, no errors.

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

This is not the problem because the real name of the function is in spanish
'isNotFormula' is in fact 'NoEsUnaFormula'. I only translated it fot better comprehension.

 

[Norie]

Why do you say it's not the problem?

Using a user-defined function like this can be tricky.

Can you explain in words what the code/formatting is supposed to do?

There might be a better approach, even if there isn't it would give us better understanding of what you want to do.

 

[SamTYler]

"=IsNotFormula(rRango)" IS a String. Until it, (the String,) is pasted into the Conditional Format box

 

[mailmaster]

Can you explain in words what the code/formatting is supposed to do?

As I said in my post, I have a sheet that imports its data from other sheets in the way:

='[Workbook1.xls]worksheet1'!C3

Sometimes I must to modify some of this data manually, so I try to set a visual way to see which data is modified manually and which data is "automatically" imported from other sheets or in other words I try to see when I applied my criteria over the imported data.

I think it must be a condicional formating, I know how to do it with a

Sub Worksheet_Change(ByVal Target As Range)

checking all cells in the ranges and modifying the format in those cases that are NOT a formula but I think it is better to do it via conditional formatting. May be I am wrong.
Sorry for my poor english, I am trying to be as clear as I know...

 

[EducatedFool]

Function IsNotFormula(rRango As Range) As Boolean
    IsNotFormula = Not rRango.HasFormula
End Function

Sub main()
    Dim rRango As Range: Set rRango = [a1:d5]
    rRango.FormatConditions.Delete
    rRango.FormatConditions.Add Type:=xlExpression, Formula1:= _
                                "=IsNotFormula(RC)"
    rRango.FormatConditions(1).Interior.ColorIndex = 36
End Sub

 

[mailmaster]

Function IsNotFormula(rRango As Range) As Boolean
    IsNotFormula = Not rRango.HasFormula
End Function

Sub main()
    Dim rRango As Range: Set rRango = [a1:d5]
    rRango.FormatConditions.Delete
    rRango.FormatConditions.Add Type:=xlExpression, Formula1:= _
                                "=IsNotFormula(RC)"
    rRango.FormatConditions(1).Interior.ColorIndex = 36
End Sub

This way doesn't give the error but it is not functioning: the function IsNotFormula is never called.

 

[xenou]

Personally I would not want to use a conditional format with a UDF in it. Better to run a macro from time to time as needed or use the change event as you mentioned in an earlier post. If you just checked for formula vs. value on the cell changes, you catch any occasions when you change a formula to a value and vice versa. Another trick you might be able to use is just to display the formulas when you want to check. Push Ctrl + ~ at the same time (~ is under the Esc key). This will show cell contents as seen in the formula bar. (In XL2003 you can also do this from tools | options view formulas and in XL2007 toggle show formulas on the formulas tab. You will quite easily see the formulas and values. But this isn't very pleasant if you want this information visible when doing your regular work.

Personally, I should drop a macro into a right click menu in the workbook, and run it when I want to see the cells that have formulas.

Anyway - I did try to get your macro to work and so far no luck for me either. Plus, I'm having a horrible time with XL2007 conditional formatting. Can anyone tell me where to find the actual conditional formula? Below, where I'd expect to see a formula all I see is "graded color scale". How it that a "rule"? (BTW, I never asked for a gradient color either - why did Excel decide on that "for me"? This is loco).


<img alt="CF" src="http://northernocean.net/etc/mrexcel/20100614_cf.png" />

 

[RoryA]

It seems to be the HasFormula property that causes the issue (I'd call it a bug myself). If you change your UDF to this:

Function IsNotFormula(ByVal rRango As Range) As Boolean
    IsNotFormula = Not (Len(rRango.Formula) > 0 And Left(rRango.Formula, 1) = "=")
End Function

it seems to work OK. You could also use a defined name and the XLM GET.CELL function instead.

 

[mailmaster]

It seems to be the HasFormula property that causes the issue (I'd call it a bug myself). If you change your UDF to this:

Function IsNotFormula(ByVal rRango As Range) As Boolean
    IsNotFormula = Not (Len(rRango.Formula) > 0 And Left(rRango.Formula, 1) = "=")
End Function

it seems to work OK.

That's the solution, thank you very much.

I'm thinking that my "paranoid" first version of the function was so near to the solution:

Function IsNotFormula(rRango As Range) As Boolean
   IsNotaFormula = True
       If rRango.HasFormula = True Then
               IsNotFormula = False
               If Left(rRango.Formula, 1) = "=" Or Left(rRango.Formula, 1) = "-" Or Left(rRango.Formula, 1) = "+" Then
                     IsNotFormula = False
               ElseIf rRango.Formula = rRango.Text Then
                      IsNotFormula = True
               End If
        End If
End Function

 

[RoryA]

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.