Microsoft Excel - I am trying to use conditional formatting for multiples of values.

I am trying to do an overhaul on our order forms. Lets say A1 is Quantity per case and B1 is quantity ordered. I want B2 to turn red if it is not a multiple of A1. This will let my customers know that they need to adjust their order if possible. Does anyone have any suggestions?

Conditional formatting enables you to highlight cells with a certain color, depending on the cell’s value.

For example, highlighting positive values with green background color, and negative values with red.

(actually not only the color can be set, but every aspect of cell formatting can be applied this way - font style, size, bold, underline etc.)

Follow these links-

http://www.contextures.com/xlcondformat03.html
http://www.cpearson.com/excel/cformatting.htm
http://www.free-training-tutorial.com/conditional-formatting.html

Hope this will help you.

None of those sights tell me how to limit the formatting to multiples of a number. 

You can use something like this in conditional formatting:

=mod(b1, a1) <> 0

The mod function returns the remainder after dividing B1 by A1.  If the remainder is anything other than 0, B1 is not a multiple of A1.  It will return a #DIV/0 if A1 is zero.

This can be dealt with:

=iferror(mod(b1,a1)<>0,True)

which will return a true when A1 is a zero (meaning they probably haven't filled it in yet).

Hi Luke


Select B1:B10 and try the below conditional formatting formula. Any errors will be handled by CF itself.

=MOD(B1,A1)

The MOD function can be used to divide numbers in Excel. Unlike regular division, however, the MOD function only gives you the remainder as an answer.

The syntax for the MOD function is:

= MOD ( Number , Divisor )

Example Using Excel's MOD Function:

    * Select all the cells in your list.
    * From the menu, select Format > Conditional Format
    * In the Conditional Format dialog, change the first dropdown from "Cell Value Is" to "Formula Is".
    * Enter this formula in the Formula box:
    =MOD(A1,B1)<>0
    * Click the Format... button
    * In the Format Cells dialog, Click the Patterns tab
    * Choose a Red pattern. Click OK.
    * Click OK to close the Conditional Formatting dialog

Hi Luke Mueller,

I have another alternative, with can give more information to the customer when they input the qty into the cell. I think the Menu>Data>Validation is a proper one to solve your problem.
With data validation....
1) customer can get the restriction / information, how to before input wrong qty.
2) when the qty input is not correct then they get the error message to inform and suggest them to next step.
3) the quantity will always be correct, with style "Stop", the wrong qty will be blocked.

Hope this help..

Pichart Y.