VBA Conditional Format

[Gliffix101]

Hello,

I'm attempting to add conditional formatting based on a value and then filter on all cells NOT conditional formatted. I'm getting hung up on the .FormatConditions.Add line. Can someone take a look and let me know what I'm missing?

With Columns("AI:AI")
    .FormatConditions.Add Type:=xlExpression, Criteria1:=Array("Initiated", "Outstanding", "Requested", "No Tracking Item Created")
    With .FormatConditions(.FormatConditions.Count)
        .SetFirstPriority
        With .Interior
            .Color = RGB(255, 217, 102)
        End With
        StopIfTrue = False
    End With
End With


Rng.AutoFilter Field:=35, Criteria1:=RGB(255, 217, 102), Operator:=xlFilterNoFill

 

[Norie]

What condition are you trying to set with that code?

If you wanted the condition to check if the values in the array existed in the cell I would expect to see a formula that used MATCH or something similar rather than just an array of values.

 

[Gliffix101]

Hi Norie

If the Cell Value in Column AI equals Initiated, Outstanding, Requested, or No Tracking Item Created I want it to highlight the cell. Then, later in the code, I filter these out.

Ultimately, I'm trying to highlight those and then filter them out so I can copy all of the rest of the cells. I'm beginning to think the xlFilterNoFill might not work either?

 

[Gliffix101]

Hi Norie,

Thank you for pointing out the formula piece. With some additional research, I found what works:

With Columns("AI:AI")
    .FormatConditions.Add Type:=xlExpression, Formula1:="=OR(ISNUMBER(SEARCH(""Outstanding"",$AI1)),ISNUMBER(SEARCH(""Initiated"",$AI1)),ISNUMBER(SEARCH(""Requested"",$AI1)),ISNUMBER(SEARCH(""No Tracking Item Created"",$AI1)))"
    With .FormatConditions(.FormatConditions.Count)
        .SetFirstPriority
        With .Interior
            .Color = RGB(255, 217, 102)
        End With
        StopIfTrue = False
    End With
End With

I also was trying to figure out how to filter out the non-highlights, not thinking about just filtering on those not filtered (naturally filled white). So I used this code:

Rng.AutoFilter Field:=35, Criteria1:=RGB(255, 255, 255), Operator:=xlFilterCellColor

Thank you for helping me think about this differently.

Bill