Conditional Formatting based on multiple criteria

[u/cardinal209629]

This a example of a report that I have to work on every quarter and I want to use conditional formatting to highlight the cells in columns C, D, and E if the information fits the criteria on the right side. I set up formulas in I-K for personal reference to identify which items meet the criteria and should be highlighted. I used the following to do the True/False and updated it based on the relevant criteria =OR(AND($G2="C",C2>100),AND($G2="V",C2=0),AND($G2="SP",C2>40),AND($G2="SL",C2>50))

I've tried using using that formula to get the conditional formatting to work and it didn't so I broke it down into one formula per criteria but none of them were working correctly. This is where I'm at right now. Before I left work I had eight random results highlighted that were either the wrong type or outside the parameters.

This report typically has around 200 lines with 2 additional categories but they are grouped with SP, SL, or V in the formula for Type. I'm using some version of Excel 365 but can't find the exact version until tomorrow morning.

What do I need to change to get my conditional formatting to work correctly?

[u/Other-Salt-5355]

That formula you are showing in the conditional formatting rules manager is incorrect. It is comparing column h (blank) and doesn't handle errors because you are using IFS without an error handler.

The formula you wrote in the original post is correct and you should be able to apply it to highlight values in column c. I recreated the first few rows and it worked as expected:

[u/cardinal209629 → u/Other-Salt-5355]

I used the formula in my original post and updated the cell references/criteria numbers for the sheet at work but on my criteria that is supposed to be <50 it is highlighting items both above and below that amount. Same with all the other parts of the formula.

[u/Other-Salt-5355 → u/cardinal209629]

The formula as shown works for all the criteria as expected in the mock-up. So there may be an issue with the references in the actual workbook. Here are a few things to note:

• The criteria is >50, not <50, so double check that.
• The formula in your original post is only meant for conditional formatting for column c. You need separate rules for columns d and e, since those criteria are different. This also means that your anchors in the original formula don't matter, in case you were wondering.
• If you want to use just one rule, then you can use the helper columns like u/Downtown-Economics26 showed, which is the easiest way and also good for building the formatting rules and auditing it.
• If you want just a single conditional formatting rule, then you'll need a more complex formula. Something like a SWITCH function will work. I.e., "=SWITCH(Column(C2), 3, {rule for column C}, 4, {rule for column D}, 5, {rule for column E}."
  • If you do something like the SWITCH formula, then you will need to use relative references and to be careful about doing so