2007 FormatConditions and FormatCondition objects

[Yard]

Hi all,

Frustrated with the ridiculous XL2007 propagation of conditional formats when users are copying & pasting cells, I thought I'd construct a CF auditing routing which would identify any CFs in a given range which apply to only one cell (it was a reasonable starting point given their application).

However, I'm confused by the behaviour of the FormatCondition object's properties.

I have a cell A1 which contains 3 conditional formats.
i) Cell value = 1 (applies to just A1)
ii) Cell value >= 2 (applies to A1:B1)
iii) Cell value = 3 (applies to A1:C1)

Simple. I want to know, for each cell in the range A1:C1, and for each format condition in that cell, some properties so I can decide which ones to delete. I wrote this to list the properties:

Sub ListCF()
Dim Wks As Worksheet, Wkb As Workbook, WksLog As Worksheet
Dim strTitle As String
Dim rnglook As Range, rngC As Range
Dim FmtC As FormatCondition
Dim k As Long
Set rnglook = Application.InputBox("Select the range you want to search", Type:=8)
If rnglook Is Nothing Then Exit Sub
Set Wks = rnglook.Parent
Set Wkb = Wks.Parent
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Set WksLog = Wkb.Worksheets.Add
Wks.Activate
WksLog.Cells(Rows.Count, 1).End(xlUp) = "Set to search in range " & rnglook.Address(0, 0)
                    
For Each rngC In rnglook
        k = 0
        WksLog.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) = WorksheetFunction.Rept("/", 100)
        WksLog.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) = "Looking at formatting in cell " & rngC.Address(0, 0)
        For Each FmtC In rngC.FormatConditions
                k = k + 1
                WksLog.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) = WorksheetFunction.Rept("-", 50)
                WksLog.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) = "Format condition priority : " & FmtC.Priority
                WksLog.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) = "Condition " & k & " applies to " & FmtC.AppliesTo.Count & " cells"
                WksLog.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) = "Condition " & k & " applies to " & FmtC.AppliesTo.Address(0, 0)
                WksLog.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) = "Formula is " & FmtC.Formula1
                WksLog.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) = "Operator is " & FmtC.Operator
        Next FmtC
Next rngC
 
WksLog.UsedRange.EntireColumn.AutoFit
WksLog.Name = "FC_Log_" & Format(Time, "hhmm_ss ")
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

But the results I get are strange. For each FormatCondition, the Priority & AppliesTo properties seem OK, but the Formula1 and Operator appear to return the Formula1 and Operator values for the FormatCondition which is being applied to the cell, rather than the rules for the FmtC object I am testing.

Can anyone give me a steer on this, or suggest another way of dealing with this pesky Excel 2007 behaviour?!

Thanks
Yard