通过VB6条件格式化Excel文档（带覆盖格式的问题） [英] Conditional Formatting Excel document via VB6 (issue with overwriting formats)

问题描述

我正在运行时创建一个Excel文档，其中包含一些我有条件格式化的值。在进行从头开始的各种尝试以及使用/修改从Excel的宏记录器输出的代码时，我有一个与格式化覆盖相关的一致问题。

I'm creating an Excel document at runtime that has a bunch of values I'd like to have conditionally formatted. In going through various attempts from scratch as well as using/modifying code outputted from the Excel's macro recorder, I'm having a consistent issue related to formatting overwrites.

ve发布了下面的代码片段，可以说我已经测试过，以确保我的选择范围是有效的，适合我想要有条件地格式化。有一些重叠，但奇怪的是，第一个条件格式只接受第二个条件格式的一个属性。含义D5：工作表的结尾最终具有绿色字体，而不是红色。评论代码的每个部分都允许他们独立工作，但我猜这是一个问题，进一步指定条件格式？我尝试了一些不同的情况，下面是修改的代码：

I've posted a snippet of the code below and can say that I've tested to ensure my selection ranges are valid and appropriate for what I want conditionally formatted. There is some overlap but what's bizarre is that the first conditional format takes on just one property of the second conditional format. Meaning D5:End of the worksheet ends up having a green color font as opposed to the red it should be. Commenting each section of the code does allow them to work independently but I'm guessing this is an issue with specifying conditional formats further somehow? I've tried a few different case scenarios and below is the code with modifications:

编辑（更新的代码）：

'First conditional format, check sheet for values > 50 and make text red.
With xl.range("D5:" & theLastColumn & lastRow)
  .FormatConditions.add Type:=xlCellValue, Operator:=xlGreater, Formula1:="=50"
  With .FormatConditions(1).Font
    .Color = -16383844
    .TintAndShade = 0
  End With
  .FormatConditions(1).StopIfTrue = False
End With


'Second conditional format, check specific row (row 5 in the example) 
'for values > 40, and fill interior with green in addition to dark green text.
With xl.range("D" & Infectivity & ":" & theLastColumn & Infectivity)
  .FormatConditions.add Type:=xlCellValue, Operator:=xlGreater, Formula1:="=40"
  With .FormatConditions(2).Font
    .Color = -16752384
    .TintAndShade = 0
  End With
  With .FormatConditions(2).Interior
    .PatternColorIndex = xlAutomatic
    .Color = 13561798
    .TintAndShade = 0
  End With
End With

那么，有多种条件格式（可能重叠的范围）的最佳方法是什么，并且仍然具有所有的功能？我已经尝试调试这么多，我确定有一些容易我忽略。我还尝试了一些不同的方法来指定单独的格式条件（1）和格式条件（2），但仍然会收到奇怪的问题。

So what's the best way to have multiple conditional formats (that may overlap ranges) and still have them all function as intended? I've tried debugging this so much I'm certain there's something easy I'm overlooking. I've also tried a few different methods to specify separate formatconditions(1) and formatconditions(2) but still receive strange issues.

编辑：

VBA代码，我继续遇到同样的问题。

VBA Code where I continue to have the same issue.

Sub conditionalFormat()
  With Range("D5:BA9")
    .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:="=50"
    .FormatConditions(.FormatConditions.Count).SetFirstPriority
    With .FormatConditions(1).Font
      .Color = -16383844
      .TintAndShade = 0
    End With
    .FormatConditions(1).StopIfTrue = False
  End With

  With Range("D9:BA9")
    .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:="=40"
    With .FormatConditions(2).Font
      .Color = -16752384
      .TintAndShade = 0
    End With
    With .FormatConditions(2).Interior
      .PatternColorIndex = xlAutomatic
      .Color = 13561798
      .TintAndShade = 0
    End With
    .FormatConditions(2).StopIfTrue = False
  End With    
End Sub

即使使用适当（红色文本）条件格式的SetFirstPriority，它只会被覆盖。我在这里缺少一些东西？

Even with the SetFirstPriority on the appropriate (red text) conditional format, it just gets overwritten somehow. Am I missing something here?

推荐答案

经过多番思考和重新编写代码，我们得出结论，我在做什么条件重叠）是混合结果的原因。在最简单的层面上，我可以将.FormatConditions.Delete添加到我的附加条件格式，以确保只应用了一种格式。

After much thought and reworking the code we came to the conclusion that what I was doing (multiple conditions overlapping) was the cause of the mixed results. At the simplest level, I was able to add .FormatConditions.Delete to my additional conditional formats to ensure only one format was applied.

修正的最终代码如下所示：

The corrected final code is shown below:

Dim Infectivity As Long
Infectivity = Application.WorksheetFunction.match("Infectivity", range("A1:" & "A" & lastRow), 0)

With xl.range("D5:" & theLastColumn & lastRow)
    .FormatConditions.add Type:=xlCellValue, Operator:=xlGreater, _
    Formula1:="=50"
    .FormatConditions(.FormatConditions.count).SetFirstPriority
With .FormatConditions(1).Font
    .Color = -16383844
    .TintAndShade = 0
End With

    .FormatConditions(1).StopIfTrue = False
End With

If Infectivity > 0 Then
With xl.range("D" & Infectivity & ":" & theLastColumn & Infectivity)
    .FormatConditions.Delete
    .FormatConditions.add Type:=xlCellValue, Operator:=xlGreater, _
     Formula1:="=40"
With .FormatConditions(1).Font
    .Color = -16752384
    .TintAndShade = 0
End With
With .FormatConditions(1).Interior
    .PatternColorIndex = xlAutomatic
    .Color = 13561798
    .TintAndShade = 0
End With
.FormatConditions(1).StopIfTrue = False
End With
End If

我的垮台与宏记录器有关，给我一个错误的格式化这些单元格的理想方法。在向前推进之前，最好先简化。

My downfall was related to the macro recorder giving me a false of the ideal method of formatting these cells. It's always best to simplify before moving forward.

主要感谢Siddharth Rout所有的帮助。

Major thanks to Siddharth Rout for all the help.

这篇关于通过VB6条件格式化Excel文档（带覆盖格式的问题）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！