# Conditional Formatting Multiple Criteria

#### dch5876

##### New Member
I want to apply conditional formatting to column M based on the wavelength in column G. I do not want to compare it to the actual wavelength but a criteria based on the different wavelength. So 1310 would have have max value of .4500, 1550 would have have a max value of .2500 and 1625 would have a max value of .3500. I want any value in column M that exceed the max value of the wavelength of column G to turn red and any value that is equal to the max value or below turn green.

I was thinking an OR statement but cant find a configuration that works.

=OR(G2=1550 =< 0.2500, C2=1625 =< 0.3500) Green
=OR(G2=1550 > 0.2500, C2=1625 < 0.3500) Red

 G M 1 Wavelength Status 2 1310 0.2011 3 1550 0.1916 4 1625 0.1984

<tbody>
</tbody>

### Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

#### Eric W

##### MrExcel MVP
If you want an OR statement, consider the formula in H2.

To get the opposite function for your red condition, you can just wrap that formula in a NOT (I2). It will also turn red if the value in G2 isn't 1310, 1550, or 1625. However, I'd recommend building a table with your values, like I did in O1:P4, then the formula you need would be much simpler (J2).

Hope this helps!

#### dch5876

##### New Member
Eric,
Appreciate the response, but was hoping to take care of this using conditional formatting. I originally wrote an IFS statement in column O and applied conditional formatting to the cells. (See Below) But wanted to try and not have hidden columns as others will be pasting most of the contents into the worksheet.

=IFS(H2=1310, 0.41,H2=1550, 0.251,H2=1625, 0.351)
Conditional Formatting
Cell Value<\$O2 Green
Cell Value>=\$O2 Red

#### Eric W

##### MrExcel MVP
All 3 of the formulas are designed to work in Conditional Formatting. Select range G2:G4 (or whatever your whole range is), click Conditional Formatting > New Rule > Use a formula > and enter one of the formulas. I only showed them on the sheet to show the results, but you don't need to use a helper column.

Incidentally, I don't use IFS much, but you can use this as your green CF formula too:

=IFS(G2=1310,M2<0.45,G2=1550,M2<0.25,G2=1625,M2<0.35)

Last edited:

#### dch5876

##### New Member
Thanks Eric! Worked like a charm. I also like the added benefit of it calling out wavelength.

Replies
11
Views
439
Replies
5
Views
159
Replies
1
Views
65
Replies
5
Views
161
Replies
8
Views
621

1,141,203
Messages
5,704,931
Members
421,372
Latest member
Jamie11

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back