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?

Thanks in advance

**Conditional formatting enables you to highlight cells with a certain color, depending on the cellâ€™s value.
For example,**

**(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.

I need the cell to turn red if it is not divisible by 12.

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).

=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)**

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

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

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.

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.

**Use Excel conditional formatting to highlight items that are in a list on the worksheet.:**

- Create a list of items you want to highlight. If the items are on a different sheet than the conditional formatting, name the list.
- Select range A2:A7
- Choose Format|Conditional Formatting
- From the first dropdown, choose
*Formula Is* - For the formula, enter

**=COUNTIF($C$2:$C$4,A2)**or, if the list is named, use the name in the formula:

=COUNTIF(CodeList,A2)

- Click the Format button.
- Select a font colour for highlighting.
- Click OK, click OK

**Follow this link=**

http://www.contextures.com/

Hope this will help you.