# Excel: Conditional Formatting using Inequalities and Values

My question is in relation to excel conditional formatting. Can you use a formula in conditional formatting to highlight cells in column M based on the inequality signs and the values entered in columns G - L? ``````     G         H          I           J         K         L    |   M
Green       Green      Amber       Amber      Red      Red   |Oct - 19
<=        100         <=         99.9        <         99  |
<=        100         <=         88.4        <         50  |
=          0         >            0         >         1   |
``````

As you can see, columns G, J and K have inequality signs (<, >, <=, >=, <> or =). Columns H, J and L include values or numbers. Can you use conditional formatting on column M "Oct - 19" based on the green, amber and red thresholds specified in columns G - L?

I know you can use the below formula (to highlight the cell green) however this will not apply as the inequality signs change for each test (as you can see, I have over 200 rows and I would like not to include 627 different rules in this file).

=M2<=\$H2

Select M2:M5 (or more, or even whole column).

Open Conditional formatting - Using formula.

Enter the formula `=COUNTIFS(M2;K2&L2)=1` and specify some format.

Then enter one more rule using formula `=COUNTIFS(M2;I2&J2)=1` with another format, and 3rd rule `=COUNTIFS(M2;G2&H2)=1` with 3rd format. PS. Last 2 symbols `=1` in formulas are, of course, excess.

• Awesome work! Thank you. – Iza Bella Nov 7 '19 at 13:51