Multiples of a # formula

**Alpha Zero** · 10-13-2008, 02:30 PM

Is it possible to have a forumla that gives a True or False (perferably if it's possible for a conditional formatting to change colors) for multiples of a number.
I would input a number in the cell and it would change the cell color if it not a multiple of say for example 13.

**VBA Noob** · 10-13-2008, 02:33 PM

Does this work

=MOD(A1,13)=0

VBA Noob

**Alpha Zero** · 10-13-2008, 02:44 PM

Let me attach a mock up of what I am trying to accomplish.
That works just fine, but I'm trying to make it look a bit more refined.
I guess if what I'm trying to get doesn't work, then I can work with that.

EDIT: Ok, on the attachment, depending on what item you input into the screen, there are different ordering requirements. I want the QTY cell to change color to RED if the QTY inputed is not a correct QTY. I would probably have to modify the way the order requirements are listed, but that's fine, as long as the end result is the field turning red when the entered amount is incorrect.

**daddylonglegs** · 10-13-2008, 04:02 PM

Let's say you put the multiple, if any, in J3:J8, e.g. for your example J5 would be 25 and J6 would be 20.....and in K3:K8 you put the minimum order, if any, e.g. for your example K7 would be 50.

Then to highlight any values in B3:B8 which don't meet both requirements, select that range and use this formula within conditional formatting

=OR(MOD(B3,J3)<>0,B3<K3)

format as required > OK

**Alpha Zero** · 10-13-2008, 04:49 PM

That works only if I manually input it into the field. Is there a way to do that when the information is pulled from a different sheet? I've made the neccessary changes to the test sheet.
As not the same items are ordered each time, this order entry sheet is for inputing only the items being ordered this specific time. It then pulls the description and order requirements from a different sheet. The problem I think it runs into is that if the information is blank, it automatically fills it with a 0.

2ND EDIT: I got it working. Just needed to split the formula into two and add both for the conditional formating!
THANKS A BUNCH!

3RD EDIT: Ok, it doesn't work. lol

**daddylonglegs** · 10-13-2008, 07:37 PM

Your column H in PO summary is text formatted [ you can verify that by putting this formula in any cell =ISNUMBER(H5)]

Because of that you are not getting the correct results for the comparison with column K [any text is deemed to be greater than any number].

You could remedy that by changing the formula in K3 to

=VLOOKUP(A3,'PO Summary'!A5:H9,8,0)+0

The +0 at the end converts a text formatted number to a true number....