Excel 2007 VBA中的重叠范围的条件格式化 - bug? [英] Conditional formatting of overlapping ranges in Excel 2007 VBA - bug?
问题描述
目前正在尝试帮助关于这个问题 - 但偶然遇到一个非常奇怪的问题:
当尝试在重叠范围(在VBA中)添加条件格式时,Excel 2007会产生错误1004或或错误9(下标超出范围)错误。我设法将错误的代码解压缩到这一点:
Sub Produce1004()
Cells.FormatConditions.Delete
范围(A1)。FormatConditions.Add类型:= xlExpression,Formula1:== 1
范围(A1:A2)。FormatConditions.Add类型:= xlExpression,Formula1:== 1
Range(A1:A2)。FormatConditions(Range(A1:A2)。FormatConditions.Count).Font.ColorIndex = 7
End Sub
Sub ProduceError9 ()
Cells.FormatConditions.Delete
范围(A1:A3)。FormatConditions.Add类型:= 2,Formula1:== 1
范围(A1:A2 ).FormatConditions.Add类型:= 2,Formula1:== 1
范围(A1:A2)。FormatConditions.Add类型:= 2,Formula1:== 1
范围(A1:A2)FormatConditions(Range(A1:A2)。FormatConditions.Count).Font.ColorIndex = 3
End Sub
这两个subs的最后一行导致错误。这个错误只发生在Excel 2007中,它在2010年运行正常。
有人知道解决方法吗?
我可以看到Produce1004()中的问题:
A1有2种格式条件,A2有1种格式条件。 >
范围(A1:A2)。FormatConditions.Count给出A1的计数,对于A2,FormatConditions(2)不存在,因此出现错误。
但是对于ProduceError9(),A1和A2的格式条件数量相同。
通过一些实验,我可以通过推导使用格式条件存储范围(设置[A1] .FormatCondition(3)的字体)也会失败)来解释。
必须更改格式条件定义的范围的格式。
可能,Excel 2010通过拆分格式条件来改善此情况飞行。
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 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.
这篇关于Excel 2007 VBA中的重叠范围的条件格式化 - bug?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!