使用 Excel VBA 代码的条件格式 [英] Conditional Formatting using Excel VBA code

问题描述

我有一个名为 DistinationRange 的 Range 对象，它包含对范围 B3:H63

的引用

我想使用 Excel VBA 代码动态应用以下两个条件格式规则.(因为范围不会一直都一样)

<块引用>

1. 如果单元格列 D 为空，则不应应用任何格式(需要在那里使用 Stop If True)
2. 如果 E 列单元格中的值小于 F 列单元格中的值，则整行应具有绿色背景.

我尝试了很多使用录音，但没有正确录音.

请帮助.

解决方案

这将为您提供简单案例的答案，但您能否详细说明您将如何知道哪些列需要比较(B 和 C在这种情况下)以及初始范围(在这种情况下为A1:D5)是多少?然后我可以尝试提供更完整的答案.

Sub setCondFormat()范围(B3").选择带范围(B3:H63").FormatConditions.Add Type:=xlExpression, Formula1:= _"=IF($D3="""",FALSE,IF($F3>=$E3,TRUE,FALSE))"随着 .FormatConditions(.FormatConditions.Count).SetFirstPriority与 .Interior.PatternColorIndex = xlAutomatic.颜色 = 5287936.TintAndShade = 0结束于结束于结束于结束子

注意:这是在 Excel 2010 中测试的.

根据评论更新代码.

I have Range object called DistinationRange which contains reference to range B3:H63

I want to apply the following two conditional formatting rules using Excel VBA code dynamically. (Because the range would not be same all the time)

1. If Cell column D is blank, no formatting should be applied (Need to use Stop If True there)
2. If Value in Cell of column E is lesser than value in cell of column F, that whole row should have green background.

I tried a lot using recording but it's not recording properly.

Kindly Help.

解决方案

This will get you to an answer for your simple case, but can you expand on how you'll know which columns will need to be compared (B and C in this case) and what the initial range (A1:D5 in this case) will be? Then I can try to provide a more complete answer.

Sub setCondFormat()
    Range("B3").Select
    With Range("B3:H63")
        .FormatConditions.Add Type:=xlExpression, Formula1:= _
          "=IF($D3="""",FALSE,IF($F3>=$E3,TRUE,FALSE))"
        With .FormatConditions(.FormatConditions.Count)
            .SetFirstPriority
            With .Interior
                .PatternColorIndex = xlAutomatic
                .Color = 5287936
                .TintAndShade = 0
            End With
        End With
    End With
End Sub

Note: this is tested in Excel 2010.

Edit: Updated code based on comments.

这篇关于使用 Excel VBA 代码的条件格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！