[RESOLVED] Condition to check if a specific cell value exceeds some value

In brief:

I monitor a specific cell value for changes (Worksheet_Change event).

If the cell value will exceed 10 000, I'd like to be informed about it (Msgbox "Your value has just exceeded 10 000").

That's not all:

I'd like to be informed about each time the cell value will exceed another 10 000 (a multiple of 10 000). For example (MsgBox "Your value has just exceeded another 10 000")


For example:

The value is:
9 000 - nothing happens
10 001 = 9 000 + 1 001 - Msgbox "Your value has just exceeded 10 000"
11 000 = 10 001 + 999 - nothing happens
15 000 = 11 000 + 4 000 - nothing happens
20 100 = 15 000 + 5 100 - Msgbox "Your value has just exceeded another 10 000"
20 500 = 20 100 + 400 - nothing happens

etc.

So what exactly is your problem? You seem to have it figured out already! You know where the Worksheet_Change event is. You just check the Target range to see which cell it is and then action accordingly.

Is it that you only want to see the msgbox the first time that the cell exceeds 10000, and then only once when it exceeds 20000?

All you need is some booleans as flags:

VB Code:

Public ten As Boolean
Public twenty As Boolean
 
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row <> range("mycell").row Or Target.Column <> range("mycell").column Then Exit Sub
If Target.Name = Range("mycell").Name Then
    If Range("mycell") > 10000 And ten <> True Then
        MsgBox "You have just exceeded 10000"
        ten = True
    End If
    If Range("mycell") > 20000 And twenty <> True Then
        MsgBox "You have just exceeded 20000"
        twenty = True
    End If
End If
End Sub

Does that answer whatever your question was?

zaza

Thank you for you reply zaza.

I'm satisfied with the code below:

VB Code:

Option Explicit
Dim boolStart As Boolean
 
Private Sub Worksheet_Calculate()
Static curPreviousValue As Currency
 
   If Not boolStart Then
      curPreviousValue = Range("score").Value
      boolStart = True
      Exit Sub
   End If
 
   If (Range("score").Value \ 10000) <> (curPreviousValue \ 10000) Then MsgBox "You have just exceeded another 10 000"
 
   curPreviousValue = Range("score").Value
 
End Sub