条件格式VBA多个条件 [英] Conditional formatting VBA multiple conditions

问题描述

我对VBA领域非常陌生，在条件格式的VBA方面需要一些帮助.

I am extremely new to VBA world and need some assistance with the VBA side of conditional formatting.

1)我需要将条件格式应用于列(M)

1) I need conditional formatting to be applied to column (M)

• 7岁以下绿色
• 黄色(7-20岁)
• 红色大于20

具有压倒性的条件是，如果列(N)如果它声明NOPO，则我不希望应用条件格式.

With the overriding condition that if column (N) if it states NOPO, I do not want conditional formatting to be applied.

我已经计算出要使用的公式，该公式指示所需的颜色，但无法将其转换为VBA条件格式(此公式显示了什么颜色以及是否应应用条件格式.

I have worked out a formula to use that indicates what colour is required but unable to turn that into VBA conditional formatting (this formula shows what colour and if the conditional formatting should be applied.

=IF(N2="osno",IF(M2<=7,"green",IF(M2<7,IF(M2>20,"red","less than 20"),IF(M2>20,IF(M2>20,"red","less than 20"),"yellow"))),"no format")

这是我当前的VBA脚本，因为您可以毫无疑问地看到它非常凌乱并且来自录制的脚本.

This is my current VBA script, as you can no doubt see it's very messy and was from a recorded script.

    Sub Conditional()
'
' Notification_05 Macro
' Conditional Formatting
'

'
    Sheets("Final").Select
    Columns("M:M").Select

    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
        Formula1:="=8"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 5296274
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
        Formula1:="=8", Formula2:="=20"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 49407
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
        Formula1:="=20"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 470000
        .TintAndShade = 0
    ActiveWindow.SmallScroll Down:=-27
    Range("M2").Select
    With Range("M:M")
        .FormatConditions.Add Type:=xlExpression, Formula1:= _
          "=LEN(TRIM(M1))=0"
        With .FormatConditions(.FormatConditions.Count)
            .SetFirstPriority
                End With
                   End With
                   End With
End Sub

谢谢

布雷克

推荐答案

CF公式需要返回true或false:您不能使用单个公式来指定多种颜色之一，而只能确定一种颜色是否应该是否适用.您将需要三个规则，每个规则具有略有不同的公式.

A CF formula needs to return either true or false: you can't use a single formula to assign one of multiple colors, only to decide if a color should be applied or not. You will need three rules, each with a slightly different formula.

Sub Tester()

    Dim rng As Range

    Set rng = Selection

    rng.FormatConditions.Delete 'clear any existing rules

    AddRule rng, "=AND(M2=""osno"", N2<7)", vbGreen
    AddRule rng, "=AND(M2=""osno"", N2>=7,N2<=20)", vbYellow
    AddRule rng, "=AND(M2=""osno"", N2>20)", vbRed

End Sub

'utility sub: add a CF rule given the formula and a fill color
Sub AddRule(rng, sFormula, lColor)
    With Selection.FormatConditions
        With .Add(Type:=xlExpression, Formula1:=sFormula)
            .Interior.Color = lColor
            .StopIfTrue = True
        End With
    End With
End Sub

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