If statement w. Conditional formatting

**althardt** · 09-01-2011, 11:32 AM

I am trying to insert and IF statement with conditional formatting. I don't know if what I want to do is even possible, but here is what I'm looking for.

I have two columns: Units per Case (say, A1) and Order Quantity (B1). I want to say If A1 is not divisible by B1, then turn the cell red. I have messed with this for a while and still can't seem to figure it out. Any help would be greatly appreciated!

**NBVC** · 09-01-2011, 11:42 AM

Use MOD to get remainder after division, and check for 0 remainder.

e.g.

=MOD(A1/B1,1)<>0

will turn red if A1/B1 has a remainder other than 0

**althardt** · 09-01-2011, 12:11 PM

The formula seems to work, but then I try to do it for the entire column, it turns all the cells red. Is there a way to fix that?

**NBVC** · 09-01-2011, 12:12 PM

Try:

=AND(A1<>"",B1<>"",MOD(A1/B1,1)<>0)

**ChrisHowk** · 09-01-2011, 12:41 PM

I got this to work when I played around with it:

Select the first value in column A...say A2

Select Conditional Formatting > New Rule > Formula
Type =mod(A2/B2)>0 *Make sure the cells aren't absolute referenced ($A$2)
Then just drag that formula to the end of the column, making sure to only copy the formula

I tried multiple values and it worked for me.

**althardt** · 09-01-2011, 01:08 PM

Yes, that seems to work. The only problem now is it turns it red for every value that does not equal one. Say the quantity is 12, any number other than 12 turns the cell red. I want it so that any number NOT divisible by 12 turns the cell red, and any number that is divisible by 12 will keep it clear.

**NBVC** · 09-01-2011, 01:19 PM

Did you try 24, 36, 48? They should not turn red when quantity is 12.. i tested it to be true.

This is assuming the quantity is column B (so 12 is in column B, 24, 36, 48 in column A).