# Conditional Formatting question using "use formula to determine which cells to format"

#### woody186

##### New Member
I am attempting to use conditional formatting using an if or statement while also including an and statement. Upon completion of the statement the selected cells aren't shading. I entered the formula in a separate cell to see if I could get it to work and it did. Below is the formula that I entered separately, excel added the asterisk before the "and" portion of the statement. Upon completion of the formula if the two criteria are met a true or a false is properly displayed.

=IF(OR(K5>100000,K5<-100000)*AND(OR(M5>10%,M5<-10%)),"true","false")

Below is the statement as it appears in the "Edit formatting rule" box. I have also entered it with the asterisk before "and" and the formula didn't work:

="if(OR(K5>100000,K5<-100000)and(or(M5>10%,M5<-10%)))

Below are the cells the formula applies to:

=\$K\$5:\$K\$175,\$M\$5:\$M\$175

The cells do not properly shade if the statement is true.

### Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

#### etaf

##### Well-known Member
in conditional formatting the function will return true or false - so you dont need to show that IF with a TRUE FALSE result.

=OR(K5>100000,K5<-100000), AND(OR(M5>10%,M5<-10%))). as you have an OR () which is complete - so the , after will not be understood hence , why a * may have been applied
BUT missing a function , not making sense as a function
Not sure what you exactly want
as you have a nested OR In and AND - BUT only an OR

do you want

AND ( OR(K5>100000,K5<-100000) , OR(M5>10%,M5<-10%) )
so evaluate from within

If K5 is greater than 100,000 OR less than -100,000 - BUT nothing in between -100,000 and + 100,000
is that what you want for K5
so either of those must be TRUE
same for M5
M5 is greater than 10% OR less than -10% - BUT nothing in between -10% and + 10%
so either of those must be TRUE
In order for the AND to have both the ORs must be true
to return a TRUE for conditional formatting to work]

Last edited:

#### woody186

##### New Member
in conditional formatting the function will return true or false - so you dont need to show that IF with a TRUE FALSE result.

=OR(K5>100000,K5<-100000), AND(OR(M5>10%,M5<-10%))). as you have an OR () which is complete - so the , after will not be understood hence , why a * may have been applied
BUT missing a function , not making sense as a function
Not sure what you exactly want
as you have a nested OR In and AND - BUT only an OR

do you want

AND ( OR(K5>100000,K5<-100000) , OR(M5>10%,M5<-10%) )
so evaluate from within

If K5 is greater than 100,000 OR less than -100,000 - BUT nothing in between -100,000 and + 100,000
is that what you want for K5
so either of those must be TRUE
same for M5
M5 is greater than 10% OR less than -10% - BUT nothing in between -10% and + 10%
so either of those must be TRUE
In order for the AND to have both the ORs must be true
to return a TRUE for conditional formatting to work]
Yes that is what I am trying to accomplish. I removed the if from the formula and copied the formula you have above and it is being accepted but when the result is true the cells in column K and M are not shading.

#### etaf

##### Well-known Member
if you want to colour columns K and M
then
select the range , K5:M1000
But because you have selected row 2 the formula has to include row 5
you also need to fix the columns with a \$ so that do not move

for 2007, 2010 , 2013 , 2016 , 2019 or 365 Subscription excel version
Conditional Formatting

Highlight applicable range >>
K5:M1000 - extend the rows to meet your data range of rows

Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:
AND ( OR(\$K5>100000,\$K5<-100000) , OR(\$M5>10%,\$M5<-10%) )

Format [Number, Font, Border, Fill]
choose the format you would like to apply when the condition is true
OK >> OK

BUT as you have selected column K to M , that will highlight
K, L & M cells from row 5 where a match is

you can select the range in K and then use the (windows) control or (Mac) command key to select the same rows but in M
if you only want those 2 columns to be formatted

Last edited:

Replies
0
Views
80
Replies
7
Views
161
Replies
2
Views
108
Replies
3
Views
526
Replies
11
Views
314

1,190,704
Messages
5,982,405
Members
439,777
Latest member
daleEH

### 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?

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