Conditional Formatting/Data Validation?

**1230dc** · 10-06-2009, 08:51 PM

I am fairly proficient in Excel, but can't seem to figure this one out:

How do I conditionally format a cell to highlight items that are not in correct quantities? For example, I can only order a product in multiples of 10. What if my coworkers place orders for: 10, 30, 50, 75, 90, and 102. I want it to automatically highlight the 75 and 102. It seems like it would be as simple as setting the cell value to "not equal to" 10*x (x=any number). But this doesn't work, how can I do this?

For the record, the quantities are actually more like 20,000 and 10,000...

If not conditional formatting, it would be just as good (perhaps better) if I could use data validation.

Thanks!!

**stanleydgromjr** · 10-06-2009, 10:10 PM

1230dc,

See the attached workbook "1230dc.xlsx" with Conditional Formatting in range A1:A6

Formula:
=MOD($A1,10)>0

**DonkeyOte** · 10-07-2009, 05:38 AM

I agree Validation makes more sense but perhaps coupled with Conditional Formatting (just in case) as DV can be circumvented (unintentionally most of the time).

Highlight A1 and apply custom DV rule of: =MOD(A1,10)=0

Re: Stan's CF formula - you can actually dispense with the >0 test given only 0 = FALSE in XL (ie the numeric output will dictate TRUE/FALSE)