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

 

[LazyBug]

Hello and welcome to forum, @wiseh2014!
Try

=AND($D2=0, $G2>$D2, OR($B2="EA", $B2="LF", $B2="VLF", $B2="SF", $B2="CY""))

 

[wiseh2014]

Hello and welcome to forum, @wiseh2014!
Try

=AND($D2=0, $G2>$D2, OR($B2="EA", $B2="LF", $B2="VLF", $B2="SF", $B2="CY""))

Hi, thank you for getting back to me! I tried your line but still am receiving a compile error. See image below. It seems to be catching on the values that I want the formula to look for when applying the formatting.

 

[Joe4]

If you are trying to do this in VBA, the issue is that double-quotes are used as Text qualifiers. So if you want literal double-quotes, you usually have to double-them up.
This can get really confusing. I found the following method much more successful:

Turn on your Macro Recorder and record yourself setting up an example of this rule that you want (your Conditional Formatting using your formula).
Then stop the Macro Recorder, and view the code you just recorded. Then you can copy/paste the formula piece that you need for you code, and that will ensure it is written just the way that VBA requires.

 

[wiseh2014]

If you are trying to do this in VBA, the issue is that double-quotes are used as Text qualifiers. So if you want literal double-quotes, you usually have to double-them up.
This can get really confusing. I found the following method much more successful:

Turn on your Macro Recorder and record yourself setting up an example of this rule that you want (your Conditional Formatting using your formula).
Then stop the Macro Recorder, and view the code you just recorded. Then you can copy/paste the formula piece that you need for you code, and that will ensure it is written just the way that VBA requires.

Hi, I tried this as well but maybe I am creating the conditional formatting rule wrong? I recorded one where I filter B for those specific values then create a rule for D to highlight when D is 0 and G>D. I did another where I used =AND($B2="EA",$D2=0,$G2>$D2) and tried that changing the EA for each additional value I want it for. However I cannot get either to work.

When one does work, it seems to be highlighting cells in D that are 0 but where G is not greater. Ex - It highlights cell D9 (G=0 here) where I would need it to highlight D10 (G=19 and B=VLF).

 

[Joe4]

Your line of code in red should look like this:

    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=AND($D2=0, $G2>$D2, OR($B2=""EA"", $B2=""LF"", $B2=""VLF"", $B2=""SF"", $B2=""CY""))"

Note the double-quote marks around each of the text values for B2, like I mentioned before.
You would get this exact syntax by turning on the Macro Recorder and entering Conditional Formatting rule manually.

 

[wiseh2014]

Your line of code in red should look like this:

    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=AND($D2=0, $G2>$D2, OR($B2=""EA"", $B2=""LF"", $B2=""VLF"", $B2=""SF"", $B2=""CY""))"

Note the double-quote marks around each of the text values for B2, like I mentioned before.
You would get this exact syntax by turning on the Macro Recorder and entering Conditional Formatting rule manually.

That worked, thank you so much!! Sorry, I must have misinterpreted that last message about the double quotations.

I really appreciate the support.

 

[Joe4]

You are welcome!
Glad we were able to help.

 

[wiseh2014]

You are welcome!
Glad we were able to help.

Hey, sorry one last question. I noticed lower down that the formatting still applies when the value in B isnt one that we specified. Would it make a difference if I switched the order of the function to have the OR criteria first? Or maybe replicating the whole chain of rules for each criteria, changing the value for B in each like below?

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

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

 

[Joe4]

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)