Testing a condition and generating a message box

[Rynofasho]

Hello all,

I'm trying to create a troubleshooting sheet for something that I need to do at work. Basically, I'm trying to catch a problem. I have an orderbook for a particular stock with the best bid and offers -- they are supposed to be in multiples of 100,000, however sometimes we are seeing that this is not the case. I am using the modulus operator to test any time that the values are not divisible by 100,000, so in a perfect world I am always getting a value of 0.

Id like to create a macro that looks at my range of 10 modulus cells and tests to see if they are equal to 0. If they aren't, I'd like to pop up a message box to alert me. Also I'd like the test to run continually so I suppose it would have to be set off by a worksheet change event. I attached a very basic example sheet but need to know if what im trying to do is possible in VB.

The modulus range I want to test is C9:D13 on sheet 2

[sweep]

Hi,

Rather than using a macro, could you instead use data validation to ensure that your error condition is never met?

Use Custom Data Validation, =MOD($C3,100000)=0 where C3 is the top left corner of the selected cells.

[Rynofasho]

I tried that, however I am not directly inputting values to where the modulus test is going on, it is updating based on whatever happened in C3 or whichever cell it is looking to. Therefore it was not generating the error (unless I manually tried to input a non 0 value in one of the ranges).

[sweep]

You could use this code as a start point.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rTestRange As Range, rCell As Range

Set rTestRange = Range("C9:D13")
For Each rCell In rTestRange
If rCell.Value <> 0 Then MsgBox (rCell.Address & " is not zero")
Next


End Sub

To enter this code, right click the worksheet tab and select view code. Paste the code into the VB editor that pops up, then close the window.

[Rynofasho]

Sweep, I was able to use that code and make it work with my sheet. Thanks!

[snb]

Why not using conditionalformat ?

=MOD(C3,10^5)<>0