worksheet change code not working

[nadie111]

Hello Nadie, welcome to the forum.

Please take the trouble to read the forum rules, and note the one which asks posters to wrap VBA code in code tags.

I've edited the code on this occasion for you.

Regards

Hello all,

I'm relatively new to macros, so my apologies if this is beyond basic. I had this working at one point, to my delight! but then I changed something and made an infinite loop and had to quit and now nothing is happening, sadly.

I want a message box to pop up when there is numerical input into cell Q9 of either less than 500 or less than 1000. If the cell is less than 500, I want the cell to revert to blank.

I'm still working on how to make the cell font colour red if the value is between 500 and 1000 using a macro rather than conditional formatting.

Thanks so much for your help!

Nadie

Here is my code:

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

        End Select
        
    
    End If
End If
End Sub