worksheet change code not working

Hello nadie111,

The purpose of the statement Application.EnableEvents is to enable or disable Excel from responding to any other generated events while the macro is running. This is important with events that are triggered by a change event, like changing a cell's value, or selecting a different cell.

If these other events are not disabled, you can trigger them by changing a cell's value or activating another cell in your code. The event will keep calling itself over and over until the stack overflows and generates an error. The Target's (cell's) value is changed in your code in the Case Statement. This will cause the error to occur because the event has not been disabled.

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Application.Intersect(Target, Range("Q9")) Is Nothing Then
    If IsNumeric(Target) Then
        Application.EnableEvents = False
        Select Case Target
            Case Is < 500          
              MsgBox ("  Impossible. Over 1000 is recommended.  ")
              Target = " "
            Case Is < 1000
              MsgBox ("  Over 1000 is recommended.  ")
        End Select 
        Apllication.EnableEvents = True        
    End If
  End If
End Sub

Sincerely,
Leith Ross

It worked! I think that events must have been disabled in the entire worksheet, which may have been causing me problems, because after I made the change you suggested, once again, not a single one of my macros were working. That was why I put the phrase in there in the first place.

So I inserted the "Application.EnableEvents = True" into my Private Sub Workbook_Open() and now everything runs perfectly. (I hope that insertion won't kick me later)

Once again, thank you for your help!