Excel VBA条件格式自动更改

Question:
Is there anything that would cause the Formula1 parameter of the FormatConditions.Add method to change automatically, or to change from what is hard-coded in an Excel-VBA macro?
If so, where is the documentation for this behavior?

问题:是否有任何会导致FormatConditions.Add方法的Formula1参数自动更改或更改Excel-VBA宏中硬编码的内容?如果是这样,这种行为的文档在哪里?

Description of Problem:
When applying the FormatConditions.Add method to a range, the formula does not match what is specified in the code.

问题描述:将FormatConditions.Add方法应用于范围时,公式与代码中指定的公式不匹配。

My macro code assigns a formula to a variable named ConditionalRangeFormula. After running the macro the actual conditional formatting formula does not match ConditionalRangeFormula, and the row in the formula does not match the row that was specified in the code. See the "Details" section below for more info.

我的宏代码将公式分配给名为ConditionalRangeFormula的变量。运行宏后,实际的条件格式化公式与ConditionalRangeFormula不匹配,并且公式中的行与代码中指定的行不匹配。有关详细信息,请参阅下面的“详细信息”部分。

Hypothesis:
Note 1:
I've noticed that with a range, Excel will automatically "fit" a conditional formatting formula to match the specifics for each cell in a range. For example, in a worksheet with random numbers between 1 and 10 in column A:

假设:注1:我注意到,对于范围,Excel将自动“拟合”条件格式化公式以匹配范围中每个单元格的细节。例如,在A列中随机数介于1和10之间的工作表中:

I choose column A.

我选择A栏。

I add a conditional format to column A, with a formula "=IF(A1=2,1)". The cell font is formatted bold red if this formula is true.

我向条件A添加条件格式,公式为“= IF(A1 = 2,1)”。如果此公式为true,则单元格字体将以粗体红色格式化。

Every cell in column A that contains "2" will be bold red, not just cell A1, even though the formula is just for A1.

A列中包含“2”的每个单元格都是粗体红色,而不仅仅是单元格A1,即使公式仅适用于A1。

Is it possible that in the background Excel is doing some changing of my formula in the code above, in an attempt to "guess" what the formula actually should be?

是否有可能在后台Excel中正在上面的代码中更改我的公式,试图“猜测”公式应该是什么?

Note 2:
I don't think this is a result of using too many conditional formats for a range. In Microsoft's Excel developer notes for "FormatConditions.Add Method", there is a remark that "You cannot define more than three conditional formats for a range." However, I've successfully added more than three conditional formats with no changes (see details below). Also, I've tested with all other conditional formatting commented out (inactivated), so that only one conditional format is applied, with no changes.

注2:我认为这不是为范围使用太多条件格式的结果。在Microsoft的Excel开发人员注释“FormatConditions.Add Method”中,有一句话“你不能为一个范围定义三种以上的条件格式”。但是,我已成功添加了三种以上的条件格式而没有任何更改(请参阅下面的详细信息)。此外,我已经测试了所有其他条件格式注释掉(停用),因此只应用了一种条件格式,没有任何更改。

Details:
I'm using Excel 2007 on a Win7 machine.

详细信息:我在Win7计算机上使用Excel 2007。

My code is a little more complex than the example given in the hypothesis above.

我的代码比上面假设中给出的例子稍微复杂一些。

The conditional format function is designed to check if a cell in column "AP" is blank, then apply a red outline.

条件格式函数用于检查“AP”列中的单元格是否为空,然后应用红色轮廓。

If I place a breakpoint at the With conditionalRange.FormatConditions _.add(xlExpression, , ConditionalRangeFormula) line, I can confirm ConditionalRangeFormula is correct ("=ISBLANK($AP1)"). However, after running, the conditional formatting formula for every cell in the specified range is "=ISBLANK($AP2)". This does what my code specifies.

如果我在With conditionalRange.FormatConditions _.add(xlExpression,ConditionalRangeFormula)行放置一个断点,我可以确认ConditionalRangeFormula是否正确(“= ISBLANK($ AP1)”)。但是,运行后,指定范围内每个单元格的条件格式设置公式为“= ISBLANK($ AP2)”。这就是我的代码指定的内容。

Please note the working range (ConditionalRange is the code below) actually starts with row 2 of column AP, since row 1 is a header row. As an interesting note, if I make ConditionalRangeFormula "=ISBLANK($AP2)", the conditional formatting formula for every cell in the specified range is "=ISBLANK($AP3)". Notice how the row in the formula is +1 from what is hard coded, just as in the first situation described in the previous paragraph. Interesting behavior, but I can't find documentation for this.

请注意,工作范围(ConditionalRange是下面的代码)实际上从列AP的第2行开始,因为第1行是标题行。有趣的是,如果我创建ConditionalRangeFormula“= ISBLANK($ AP2)”,则指定范围内每个单元格的条件格式化公式为“= ISBLANK($ AP3)”。注意公式中的行是如何从硬编码的+1开始,就像前一段中描述的第一种情况一样。有趣的行为,但我找不到这方面的文档。

Also, please note that there are four With...End With statements that apply conditional formatting to that cell, before the conditional formatting that gives problems is applied. Each of those first four statements use formulas that work as expected, so I've simplified those code blocks to make the overall code easier to follow. See "Note 2" under the Hypothesis section above for more details.

另请注意,在应用出现问题的条件格式之前,有四个With ... End With语句将条件格式应用于该单元格。前四个语句中的每一个都使用按预期工作的公式,因此我简化了这些代码块,使整个代码更容易理解。有关详细信息,请参阅上面“假设”部分下的“注2”。

Here is the code outline:

这是代码大纲:

'define string to identify workbook
Dim w2 As String
w2 = "myworksheet.xlsx"

'define ws2 as worksheet to work on
Dim ws2 As Worksheet: Set ws2 = Workbooks(w2).Worksheets(1)

'define working range
Dim ws2r As range
Set ws2r = ws2.range("E2", ws2.range("E2").End(xlDown))

'add conditional formatting to the working range
With ws2

  'see below for .colDiff function
  Set ConditionalRange = ws2r.Offset(0, colDiff("E", "AP")) 

  'The following 4 With...End With statements assign other
  'conditional formats, none of which have problems.
  'I've simplified these statements to outline what's being done.
  'See the last (5th) With...End With statement for
  'the unexpected behavior.

  WithConditionalRange.FormatConditions _
    .add(xlExpression, , ADifferentFormula1)
    .Font.Color = someRGBValue
  End With

  WithConditionalRange.FormatConditions _
    .add(xlExpression, , ADifferentFormula2)
    .Font.Color = someRGBValue
  End With

  WithConditionalRange.FormatConditions _
    .add(xlExpression, , ADifferentFormula3)
    .Font.Color = someRGBValue
  End With

  WithConditionalRange.FormatConditions _
    .add(xlExpression, , ADifferentFormula4)
    .Font.Color = someRGBValue
  End With

  'This With...End With block has unexpected behavior.
  ConditionalRangeFormula = "=ISBLANK($AP1)"
  With ConditionalRange.FormatConditions _
    .add(xlExpression, , ConditionalRangeFormula)
    .Borders.color = RGB(192, 0, 0)
  End With

End With 'with ws2

Here's the "colDiff" function called in the procedure above:

这是上面过程中调用的“colDiff”函数:

Public Function colDiff(col1 As String, col2 As String) As Long
  With ActiveSheet
    'return the number of columns between col1 and col2
    colDiff = Abs(.range(col1 & "1").Column - .range(col2 & "1").Column)
  End With
End Function

1 个解决方案

#1

I tested this functionality by placing a header "Data" in AP1, placing random data from AP2 to AP16, then deleting AP1,5,7,13 to make BLANKS and the following worked correctly:

我通过在AP1中放置标题“Data”来测试此功能,将随机数据从AP2放到AP16,然后删除AP1,5,7,13以使BLANKS和以下工作正常:

Public Sub Test()
    With Range("E2:AP16").FormatConditions.Add(xlExpression, , "=ISBLANK($AP2)")
        .Borders.Color = RGB(192, 0, 0)
    End With
End Sub

Does the above single function work correctly for you? If not, I would suspect that perhaps there are merged cells or some other spreadsheet specific issue going on.

上述单一功能是否适合您?如果没有,我怀疑可能有合并的单元格或其他一些电子表格特定的问题。

#1