easiest way to do this?

[viper]

I am working on a spreadsheet to enter purchases. I will have probably have 25-30 cells with a case quanity and a total dollar amount for the purchase. To prevent errors in entering I want to either have a message box notify the user of the incorrect entry and change the cell color or display the message box of the error and then clear the cells contents. To do the above I will need for each cell(say column b, rows 10 to 40) to divide its entry($100.00) by column c, rows 10 to 40) their entry(4). So I will need for b10/c10,b11/c11 and so on. The error I am looking for will be a difference of 10% in the case cost. I will probably have to have a column for the start up case cost so that each time the user makes an entry it will check his/her entry against the case price. So i will have row b as the qty., c as the dollar amount, and say e as the initial cost/case. Will I need to write a conditional format code for each cell or use the worksheet calculate event?

 

[Barrie Davidson]

Viper, I would use conditional formatting for this. You will get in to trouble if you try and use the worksheet's code. For example, if I use the worksheet's code to notify the user that there is an error, they will get this notification as soon as they enter something in column B or column C before they get a chance to enter something in the other column. Of course, if you can ensure that the users will enter in one column FIRST, then you code use code. Something like (assumes they enter column B first):

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column = 3 Then
    If Target.Offset(0, 2).Value > 0.1 Then
        MsgBox ("Incorrect amounts entered")
    End If
End If
End Sub

Hope this explanation is clear.

 

[viper]

Thanks Barrie,

I'll try it both ways and see which I like better. If I try the worksheet code I'll set it up so the second column won't except an entry until the first column has an entry.