VBA Apply Formatting Based on 2 Criteria and If Another Cell Contains...

[wiseh2014]

I'm sure it's simple but cant figure out where I am going wrong. I have some previously existing code that works fine, but I don't know how to modify this to only apply these formatting conditions if another column contains certain values.

I have a report that is generated and the code below provides conditional formatting if the value in D = 0 and value in G>D, but I only want this to happen if the value in B contains any of the following values: EA, LF, VLF, SF or CY.

I have seen people add SEARCH(""Value"',$B2) but I do not know where I would add that and if I can add all those different values into the SEARCH function.
I have tried
"=AND($D2=0,$G2>$D2,SEARCH(""EA","LF","VLF","SF","CY"",$B2))"
And also tried it without the commas between each value.

Any advice would be appreciated.

Thank you!

Range("Table13[Week Ending Units]").Select
Application.CutCopyMode = False
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND($D2=0,$G2>$D2)"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 10066431
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False

 

[wiseh2014]

I am really sure what you are asking.
I am also not really sure why you are splitting the same rule. And it doesn't really make sense to have an "OR" with only one condition. There is no point to that.

And this line is incomplete anyway, as you are missing the closing parentheses and double-quote, i.e.

"=AND($D2=0, $G2>$D2, OR($B2=""VLF"")

should be:

"=AND($D2=0, $G2>$D2, OR($B2=""VLF""))"

(but like I said, having an "OR" with just one condition is pointless)

The closing parenthases was just a typo by me in that message, sorry, I had it correct in the tool. But what I meant was that when inputting your formula, it resulted in the following image.

Line 10 is exactly what I want, value in D=0, value of G>D, value of B is one of the double quoted values we defined VLF.
However, Line 2 meets the first two criteria but the value in B (WK) is not one that we defined.

So I only want to formatting to happen if it meets both those criteria for D and G and has one of the double quotation values in B.

 

[Joe4]

When I use the formula I posted in post 6 (the one you accepted as the solution), it works properly for me:

 

[wiseh2014]

When I use the formula I posted in post 6 (the one you accepted as the solution), it works properly for me:

View attachment 109085

Sounds good, thank you. I think it may be something I made an error on earlier on in my macro and will get that corrected.

I greatly appreciate the time you spent helping me resolve this! I enjoy learning how to do this.

 

[Joe4]

Sounds good, thank you. I think it may be something I made an error on earlier on in my macro and will get that corrected.

I greatly appreciate the time you spent helping me resolve this! I enjoy learning how to do this.

You are welcome.

Yeah, I am guessing that there may be something else in your macro that may be causing that to happen. You may want to remove all Conditional Formatting, and start with just the one to confirm it is working properly.