Compare conditional formatting rules

[mcallaghan]

All,

I'm having an absolute nightmare with conditional formatting. I had a spreadsheet that updated small amounts of data by copying and pasting everytime you moved between sheets.

Thanks to Excel duplicating conditional formatting rules, the spreadsheet completely fell over because I ended up with hundreds of conditional formatting rules all over the place and everything took longer.

I've been trying, in another workbook, to develop a macro to compare formatconditions by colorindex (thanks to the incredible fact that .formula1 returns the formula of whatever condition is active, regardless of which one you are looping through) and delete duplicates. I've put message boxes all through it and it tells me where it finds duplicates. In the next line I put Formatconditions(i).delete, and then when I look at the conditional formatting left on the cell, it's all wrong. I don't understand it, can anyone help?

Sub conditions()
Dim i As Long
Dim FC As FormatCondition
Dim apto As Range
Dim rng As Range
Dim x As Integer
Dim form As String
Dim nexti As Integer

nexti = 0
Set rng = Selection
Line1:
nexti = nexti + 1
If nexti > rng.FormatConditions.Count Then GoTo Line2

'MsgBox (rng.Address)
Set apto = rng.FormatConditions(nexti).AppliesTo
interi = rng.FormatConditions(nexti).Interior.ColorIndex
x = rng.FormatConditions.Count
MsgBox ("comparing " & nexti & " = " & interi)
For i = 1 To rng.FormatConditions.Count
'On Error Resume Next
If i = nexti Then GoTo Line3
If i > x Then
Exit For
End If

MsgBox ("with " & i & " = " & rng.FormatConditions(i).Interior.ColorIndex)

If rng.FormatConditions(i).Interior.ColorIndex = interi Then
MsgBox ("Found a match " & i & " = " & rng.FormatConditions(i).Interior.ColorIndex)

rng.FormatConditions(i).Delete
nexti = nexti + 1
x = x - 1

End If
Line3:
Next

GoTo Line1
Line2:
End Sub


By the way, am on Excel 2007.