Odtnhj

I'm attempting to use a VBA function created by Bob Phillips to check if conditional formatting has been met in a particular cell. This seems to be a respectable solution, as Google turns up a number of different references to it, but it's throwing a Type error for me. I'm using Excel 2007, and the last update made to the code was in 2005, so I assume there are some compatibility issues involved. I have already fixed one syntax error (Set rng = rng(1, 1) wasn't working until I inserted .Cells) but I'm unable to fix this one because I can't parse what the code is doing.

The function is designed specifically to evaluate conditionals based on a formula, which mine is. Here is the full code:

Public Function IsCFMet2(rng As Range) As Boolean

Dim oFC As FormatCondition
Dim sF1 As String
Dim iRow As Long
Dim iColumn As Long

 Set rng = rng.Cells(1, 1)
 If rng.FormatConditions.Count > 0 Then
 For Each oFC In rng.FormatConditions
 If oFC.Type = xlExpression Then
 're-adjust the formula back to the formula that applies
 'to the cell as relative formulae adjust to the activecell
 With Application
 iRow = rng.Row
 iColumn = rng.Column
 sF1 = .Substitute(oFC.Formula1, "ROW()", iRow)
 sF1 = .Substitute(sF1, "COLUMN()", iColumn)
 sF1 = .ConvertFormula(sF1, xlA1, xlR1C1)
 sF1 = .ConvertFormula(sF1, xlR1C1, xlA1, , rng)
 End With
 **** IsCFMet2 = rng.Parent.Evaluate(sF1)
 End If
 If IsCFMet2 Then Exit Function
 Next oFC
 End If

End Function

The Type Error is thrown by the starred line. I don't fully grasp the With Application block or how it's contributing to the detection of a fulfilled conditional formatting rule, so I don't know what the problem is or how to fix it without breaking the code.

Here is the test macro I'm using to check the function:

Sub Test():

Dim Tester As Range

Set Tester = ActiveCell

 If IsCFMet2(Tester) Then MsgBox "CF is met!"

End Sub

However I've gotten the same error with various range inputs, so I'm pretty confident the input itself is not the problem - I'm including it just as a precaution.