Relative references in Excel VBA Formatconditions.Formula1

Question

Hi All,

I am having some trouble with the relative referencing, when trying to cycle through conditional formats in an Excel sheet.

What I am trying to do is loop through the conditions, evaluate them to see if they are true, and if they are, applying the background color of that condition to another cell.

I know that .formula1 has some weird behavior with the way it handles relative references - i have overcome this to some extent (all info on the web would suggest that it returns the references relative to the active cell), and i have implemented a workaround based on that.

This works fine for the first range that has conditional formatting. However, when I get into a second range, it seems to start putting the references relative to the top left cell of that range! I can't work out any simple way to stop this, but if i could work out some way of finding out what cell it is calculating the formulas relative to, i could work around it. Can anyone suggest a way to do this?

A sample of the code I am using:

Set Rng = i.Range("A1:DP60")
    End If

    For Each copyCell In Rng

        j = 1

        Do While j <= copyCell.FormatConditions.Count And copyCell.FormatConditions.Count > 0
            frmla = i.Range(copyCell.Address).FormatConditions.Item(j).Formula1
            frmlaR1C1 = Application.ConvertFormula(frmla, xlA1, xlR1C1, , ActiveCell)
            R1C1absolute = Application.ConvertFormula(frmla, xlA1, xlR1C1, xlAbsolute, ActiveCell)
            frmlaA1 = Application.ConvertFormula(frmlaR1C1, xlR1C1, xlA1, , Rng.Cells(1, 1))
            boo = Application.Evaluate(frmlaA1)
            If boo Then
                NewWorkbook.Worksheets(newWSName).Cells(copyCell.Row, copyCell.Column).Interior.ColorIndex = copyCell.FormatConditions(j).Interior.PatternColorIndex
                Debug.Print copyCell.Address & " " & copyCell.FormatConditions(j).Interior.PatternColorIndex
                Exit Do
            Else:
                j = j + 1
            End If

        Loop

    Next copyCell`

NOTE: i is the worksheet on which the ranges are located.

Any help would be appreciated.