Conditional Formatting Multiple Criteria

dch5876

New Member
Joined
Mar 17, 2017
Messages
19
Office Version
  1. 365
Platform
  1. Windows
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

GM
1WavelengthStatus
213100.2011
315500.1916
416250.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
Joined
Aug 18, 2015
Messages
11,396
If you want an OR statement, consider the formula in H2.

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th><th>O</th><th>P</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="color: #333333;background-color: #FAFAFA;;">Wavelength</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="color: #333333;background-color: #FAFAFA;;">Status</td><td style="text-align: right;;"></td><td style=";">Wavelength</td><td style=";">Max</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">1310</td><td style="text-align: right;;">TRUE</td><td style="text-align: right;;">FALSE</td><td style="text-align: right;;">TRUE</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">0.2011</td><td style="text-align: right;;"></td><td style="text-align: right;;">1310</td><td style="text-align: right;;">0.45</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">1550</td><td style="text-align: right;;">TRUE</td><td style="text-align: right;;">FALSE</td><td style="text-align: right;;">TRUE</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">0.1916</td><td style="text-align: right;;"></td><td style="text-align: right;;">1550</td><td style="text-align: right;;">0.25</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">1625</td><td style="text-align: right;;">TRUE</td><td style="text-align: right;;">FALSE</td><td style="text-align: right;;">TRUE</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">0.1984</td><td style="text-align: right;;"></td><td style="text-align: right;;">1625</td><td style="text-align: right;;">0.35</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet3</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">H2</th><td style="text-align:left">=OR(<font color="Blue">AND(<font color="Red">G2=1310,M2<0.45</font>),AND(<font color="Red">G2=1550,M2<0.25</font>),AND(<font color="Red">G2=1625,M2<0.35</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">I2</th><td style="text-align:left">=NOT(<font color="Blue">OR(<font color="Red">AND(<font color="Green">G2=1310,M2<0.45</font>),AND(<font color="Green">G2=1550,M2<0.25</font>),AND(<font color="Green">G2=1625,M2<0.35</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">J2</th><td style="text-align:left">=M2<=VLOOKUP(<font color="Blue">G2,$O$2:$P$4,2</font>)</td></tr></tbody></table></td></tr></table><br />

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
Joined
Mar 17, 2017
Messages
19
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Aug 18, 2015
Messages
11,396
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
Joined
Mar 17, 2017
Messages
19
Office Version
  1. 365
Platform
  1. Windows
Thanks Eric! Worked like a charm. I also like the added benefit of it calling out wavelength.
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top