logo标签列表

Excel VBA条件格式自动更改

vbaexcel
3
问题:
在应用FormatConditions.Add方法到一个范围时，是否有任何因素会导致Formula1参数自动更改，或者从Excel-VBA宏中硬编码的内容更改？
如果是这样，这种行为的文档在哪里？

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

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

假设:
注意1：
我已经注意到，在一个范围内，Excel会自动“适应”条件格式公式以匹配每个单元格的具体情况。例如，在列A中具有1到10之间的随机数字的工作表中：

  1. 我选择A列。
  2. 我为A列添加了一个条件格式，公式为“=IF(A1=2,1)”。如果该公式为真，则单元格字体会以粗体红色格式化。
  3. 在A列中包含“2”的每个单元格都将是粗体红色的，不仅仅是单元格A1，尽管公式只是针对A1的。

Excel是否可能在后台更改我的上述代码中的公式，试图“猜测”实际应该是什么公式？

注意2：
我认为这不是使用太多区域条件格式的结果。在Microsoft Excel "FormatConditions.Add Method"开发者注释 中，有一条备注说“您不能为一个范围定义超过三个条件格式。” 然而，我已经成功添加了超过三个条件格式，并没有任何变化（请参见下面的详细信息）。此外，我已经测试过所有其他条件格式都被注释掉（未激活），因此只应用了一个条件格式，但没有任何变化。

详细信息：
我正在使用Win7机器上的Excel 2007。

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

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

如果我在With conditionalRange.FormatConditions _.add(xlExpression, , ConditionalRangeFormula)行上设置断点，我可以确认ConditionalRangeFormula是正确的（“=ISBLANK($AP1)”）。但是，在运行后，指定范围内每个单元格的条件格式公式为“=ISBLANK($AP2)”。这正是我的代码所指定的。
请注意，工作范围（ConditionalRange是下面的代码）实际上从列AP的第二行开始，因为第一行是标题行。有趣的是，如果我将ConditionalRangeFormula设置为“=ISBLANK($AP2)”，则指定范围内每个单元格的条件格式公式为“=ISBLANK($AP3)” 。请注意，在公式中，行号比硬编码的值+1，就像前一段描述的第一种情况一样。有趣的行为，但我找不到相关文档。
此外，请注意，在应用导致问题的条件格式之前，有四个 With...End With 语句适用于该单元格的条件格式。这前四个语句中的每一个都使用按预期工作的公式，因此我已经简化了这些代码块，以使整个代码更易于理解。有关更多详细信息，请参见上面假设部分下的“注2”。
以下是代码概述：
'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

这是在上述过程中调用的“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个回答
0

我通过在AP1中放置标题“数据”，将随机数据从AP2到AP16，然后删除AP1、5、7、13以使其为空白，并且以下内容正常工作来测试了此功能：

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

上述单一函数是否对您正常工作？如果不是，我会怀疑可能存在合并单元格或其他电子表格特定问题。

感谢您的输入。是的，您的示例对我有用，但不幸的是它并没有解释问题的本质。问题基本上是，为什么电子表格会显示上述行为？但是，对于您的答案有两个想法：首先（这不是显而易见的），我只使用了一列，而不是像您的示例中那样使用多列。其次，在我的工作表中没有合并单元格。 - Aaron Thomas
@AaronThomas 抱歉，我误读了示例代码 - 我以为 colDiff 是用来扩展范围的，而不是偏移它。有趣的是，明确地使用立即值来编写代码是有效的，但通过变量隐式地进行操作却不行 :( - Blackhawk
@AaronThomas 只是让你知道，Excel 应用条件格式的“魔法”基于相对定位。Excel 将公式应用于目标范围的第一个单元格，=ISBLANK($AP1)，并将其偏移以将其应用于范围中的其余单元格。因此，由于 AP3 比第一个单元格 AP2 下移一行，所以公式变为 =ISBLANK($AP2)。如果出现某些情况，你已将条件格式应用于多个列，则由于使用了 $ 锚定，$AP 仍将保持不变。 - Blackhawk
这样做很有道理，并且符合上面的注释1。有关此事的文档吗？不幸的是，FormatConditions对象的文档没有提到太多内容。 - Aaron Thomas
@AaronThomas 同意，找不到太多官方的微软文档，但是这里有几个网站提供了一些例子，可以帮助理解条件格式化的寻址方式：这里 和 这里。 - Blackhawk
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接