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

Asked By Luke Mueller on 01-Sep-11 04:20 PM
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
Jitendra Faye replied to Luke Mueller on 01-Sep-11 04:23 PM

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.

Luke Mueller replied to Jitendra Faye on 01-Sep-11 04:32 PM
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. 


wally eye replied to Luke Mueller on 01-Sep-11 05:00 PM
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).
Jackpot . replied to Luke Mueller on 02-Sep-11 01:06 AM
Hi Luke


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

=MOD(B1,A1)
Anoop S replied to Luke Mueller on 02-Sep-11 02:06 AM
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
Pichart Y. replied to Luke Mueller on 02-Sep-11 05:49 AM
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.

Radhika roy replied to Luke Mueller on 02-Sep-11 10:03 AM

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

  1. Create a list of items you want to highlight. If the items are on a different sheet than the conditional formatting, name the list.
  2. Select range A2:A7
  3. Choose Format|Conditional Formatting
  4. From the first dropdown, choose Formula Is
  5. 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) 
  6. Click the Format button.
  7. Select a font colour for highlighting.
  8. Click OK, click OK

Follow this link=

http://www.contextures.com/xlcondformat03.html


Hope this will help you.