如何在 VBA 编码中添加多个条件格式 - EXCEL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/41011532/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to add multiple conditional formatting in VBA coding - EXCEL
提问by Joel
How do I code multiple conditional formatting ? Currently, I'm only able to code a single conditional formatting.
如何编码多个条件格式？目前，我只能编写一个条件格式。
MY CODE:
我的代码：
Sub Button5_Click()
Dim ws As Worksheet
Dim i As Integer
Set ws = Sheets("COMPARISON")
i = 1
With Range("I2:I146").FormatConditions.Add( _
Type:=xlExpression, _
Formula1:="=((($I2-$E2)/$E2)*100) > 20")
.Interior.Color = RGB(255, 0, 0)
End With
Do Until i = 300
If ws.Range("I" & i).DisplayFormat.Interior.Color = RGB(255, 0, 0) Then
msg = "I" & i & " -" & " Data has changed"
MsgBox msg
End If
i = i + 1
Loop
End Sub
I have manage to create one conditional formatting, where if the value of I2 - E2 is more than 20%, fill the cell red.
我设法创建了一种条件格式，如果 I2 - E2 的值超过 20%，则将单元格填充为红色。
I wish to create two more conditional formatting such where
我希望创建两个更多条件格式，例如 where
1) if the cell is 0 fill the cell black and set the font white
1）如果单元格为0，则将单元格填充为黑色并将字体设置为白色
2) if the cell I2 is < E2 but not 0 fill the cell yellow.
2) 如果单元格 I2 < E2 但不是 0，则将单元格填充为黄色。
Could someone help me with the other 2 conditional formatting ? Many thanks.
有人可以帮我处理其他 2 个条件格式吗？非常感谢。
回答by Rowen Chumacera
I just duplicated the Withblock and applied the conditions you have. You can try this:
我只是复制了With块并应用了您拥有的条件。你可以试试这个：
Sub Button5_Click()
Dim ws As Worksheet
Dim i As Integer
Set ws = Sheets("COMPARISON")
i = 1
With Range("I2:I146").FormatConditions.Add( _
Type:=xlExpression, _
Formula1:="=((($I2-$E2)/$E2)*100) > 20")
.Interior.Color = RGB(255, 0, 0)
End With
With Range("I2:I146").FormatConditions.Add( _
Type:=xlCellValue, _
Operator:=xlEqual, _
Formula1:="0")
.Interior.Color = RGB(0, 0, 0)
.Font.Color = RGB(255, 255, 255)
End With
With Range("I2:I146").FormatConditions.Add( _
Type:=xlExpression, _
Formula1:="=AND($I2<$E2, $I2<>0)")
.Interior.Color = RGB(255, 255, 0)
End With
Do Until i = 300
If ws.Range("I" & i).DisplayFormat.Interior.Color = RGB(255, 0, 0) Then
msg = "I" & i & " -" & " Data has changed"
MsgBox msg
End If
i = i + 1
Loop
End Sub
