get cell interior color of active conditional format met - excel 2007

[wotadude]

I need to be able to get the cell interior color of the current conditional format met.
So I can easily test if my range of cells are in a particular state based on the CF colour (eg Red is bad)

I have trialed an approach by Chip Pearson & Bob Phillips but without sucess in 2007.

'---------------------------------------------------------------------
Public Function CFColorindex(rng As Range)
'---------------------------------------------------------------------
Dim oFC As FormatCondition
Dim sF1 As String
Dim iRow As Long
Dim iColumn As Long

   Set rng = rng(1, 1)
   If rng.FormatConditions.Count > 0 Then
      For Each oFC In rng.FormatConditions
         If oFC.Type = xlCellValue Then
            Select Case oFC.Operator
               Case xlEqual
                  CFColorindex = rng.Value = oFC.Formula1
               Case xlNotEqual
                  CFColorindex = rng.Value <> oFC.Formula1
               Case xlGreater
                  CFColorindex = rng.Value > oFC.Formula1
               Case xlGreaterEqual
                  CFColorindex = rng.Value >= oFC.Formula1
               Case xlLess
                  CFColorindex = rng.Value < oFC.Formula1
               Case xlLessEqual
                  CFColorindex = rng.Value <= oFC.Formula1
               Case xlBetween
                  CFColorindex = (rng.Value >= oFC.Formula1 And _
                             rng.Value <= oFC.Formula2)
               Case xlNotBetween
                  CFColorindex = (rng.Value < oFC.Formula1 Or _
                             rng.Value > oFC.Formula2)
            End Select
         Else
             '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
            CFColorindex = rng.Parent.Evaluate(sF1)
         End If

         If CFColorindex Then
            If Not IsNull(oFC.Interior.Colorindex) Then
               CFColorindex = oFC.Interior.Colorindex
               Exit Function
            End If
         End If
     Next oFC
   End If 'rng.FormatConditions.Count > 0

End Function

I think Bob is on the right track with the substitution and conversion of expression formula, but formula returned to evaluate is not correct.

I have attached a simple CF example file
Refer cell A2. when evaluating the CF it converts formula (rule applied to range A1:A3) from "=A1=1" to "XFD1=1", where it should actually be "A2=1"

I would appreciate help to resolve this issue.