Conditional formatting of overlapping ranges in Excel 2007 VBA - bug?

Question

Currently trying to help on this question - but stumbled across a very strange problem:

When trying to add conditional formatting on overlapping ranges (in VBA), Excel 2007 produces Error 1004 or or Error 9 (Subscript out of range) errors. I managed to boil the erroneous code down to this:

Sub Produce1004()
    Cells.FormatConditions.Delete
    Range("A1").FormatConditions.Add Type:=xlExpression, Formula1:="=1"
    Range("A1:A2").FormatConditions.Add Type:=xlExpression, Formula1:="=1"
    Range("A1:A2").FormatConditions(Range("A1:A2").FormatConditions.Count).Font.ColorIndex = 7
End Sub

Sub ProduceError9()
    Cells.FormatConditions.Delete
    Range("A1:A3").FormatConditions.Add Type:=2, Formula1:="=1"
    Range("A1:A2").FormatConditions.Add Type:=2, Formula1:="=1"
    Range("A1:A2").FormatConditions.Add Type:=2, Formula1:="=1"
    Range("A1:A2").FormatConditions(Range("A1:A2").FormatConditions.Count).Font.ColorIndex = 3
End Sub

It's the last line in both subs that causes the error. The error only occurs in Excel 2007, it runs fine in 2010.

Does anybody know a workaround?

I know that VBA was rewritten for Office 2010, so that would explain the difference. Have you tried FormatConditions.Item(...) instead? — Michael Richardson
Peter, I suppose this is a bug in Excel 2007 - I already performed some testing. However, I need to additionally check this stuff for 2010 in parallel. Will get back as soon as have facts, not guesses) — Peter L.
Any more ideas on this issue? Four years later my work environment is still using Excel 2007, and I'm hitting this bug too. See question here: stackoverflow.com/q/43463037/2084052 — Zephyr Mays

grahamj42 grahamj42 · Accepted Answer · 2013-02-06T14:05:27

I can see a problem in Produce1004() :

A1 has 2 format conditions and A2 has 1 format condition.

Range("A1:A2").FormatConditions.Count gives the count for A1, FormatConditions(2) doesn't exist for A2, hence the error.

But for ProduceError9() the number of format conditions is the same for A1 and A2.

With a little experimentation, I can explain this by deducing that the range is stored with the format condition (setting the font for [A1].FormatCondition(3) also fails). One must change the format for the range for which the format condition was defined.

Presumably, Excel 2010 improves on this situation by splitting a format condition on the fly.

Conditional formatting of overlapping ranges in Excel 2007 VBA - bug?

1 Answers