Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: easiest way to do this?

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Jefferson City, Missouri
    Posts
    382
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?

  2. #2
    MrExcel MVP Barrie Davidson's Avatar
    Join Date
    Feb 2002
    Location
    Winnipeg
    Posts
    2,330
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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):
    Code:
    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.

    Barrie Davidson

    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Jefferson City, Missouri
    Posts
    382
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •