Excel - Conditional Formatting with multiple criteria and compare 2 columns

Question

Good day,

I’ve been trying to work through this, but I’m finding it difficult to complete on my own. I’ve given it my best effort, but I could really use some assistance to move forward. Your help would make a big difference, and I’d greatly appreciate any guidance you can offer.

Scenario:

highlight the cell in column K3 if the value in K3 is equal to or less than the value in I3.

Additional Criteria:

For R3 = 1: K3 should not be highlighted, even if K3 equals I3.

For R3 = 2, 3, 4, 5, or 6: If K3 is equal to or less than I3, K3 should be highlighted.

Note: K3 cell has formula "=IF(R3=1,I3,(P3*M3))"

Please see the attached photo for reference on how the final version should look.

Answer 1

Hi AAP01,

Thanks for visiting Microsoft Community.

I understand your requirements and have created a simple test dataset. I have hidden the columns that are not needed.

Your requirement can be summarized as setting conditional formatting in Excel based on the following rules:

For cells starting from column K, if column R equals 1, then if the value is less than the cell in column I, it should be filled with red; if column R does not equal 1, then if the value is not greater than the cell in column I, it should be filled with red.

The formula is as follows:

=OR(AND($R3=1, K3<$I3), AND($R3<>1, K3<=$I3))

Here are the test results:

I look forward to hearing your feedback. If you encounter any issues, please feel free to reply.

Best Regards,

Jonathan Z - MSFT | Microsoft Community Support Specialist

Answer 2

Good day,

Thank you so much! Now I understand where I went wrong—it was the positioning of the AND & OR functions, not the dollar signs as I initially thought.

Here are the formulas I’ve tried, but they’re still not working:

1. =AND($K2<=$I2, $R2>=2, $R2<=6)
2. =AND($K2<>"", $I2<>"", $K2<=$I2, $R2>=2, $R2<=6)
3. =AND($K3<=$I3, $R3>=2, $R3<=6, $K3<>"", $I3<>"")
4. =AND(K3<=I3, OR(R3=2, R3=3, R3=4, R3=5, R3=6))
5. =AND(K3<=I3, OR(R3=2, R3=3, R3=4, R3=5, R3=6), NOT(AND(K3=I3, R3=1)))

I’d appreciate your help in figuring out what might be wrong.