Excel entry must be negative integer

what i did in the following is; i selected the cells you listed and named a range as "negativesonly" then on the Worksheet_Change event for that sheet i added the following code:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    Set rng = Application.Range("negativesonly")
    Dim r As Range
    For Each r In rng
        If Target.Address = r.Address Then
            If Target.Value2 >= 0 Then
                MsgBox "Value at " & r.Address & " must be negative number" & vbCrLf & "Please enter a valid negative number", vbCritical
                Target.Select
            End If
        End If
    Next
End Sub

Select allOpen in new window

Works for me - I tried validation, but I see I made an error in my original attempt.

My brain is a bit fried.

Thanks.

Is there any way we can reopen this one and award Aruiz04 - 400 points and dlmille 100 points.  I tried the first solution and it did not totally fit my needs however - I achieved what I needed with Aruiz04's solution.

If you'd like, you can request attention to do that.  You had accepted mine before I had a chance to submit this, however, which does an UNDO so the previous entry remains.

HOWEVER - PLEASE NOTE: Aruizo4's solution still allows the change to happen, though you get a warning message.

Here's your code:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rCheckRanges As Range

    Set rCheckRanges = Union(Range("G6"), Range("G9"), Range("G10"), Range("G11"), Range("G20"))
    
    If Not Intersect(Target, rCheckRanges) Is Nothing Then
        If Target.Value >= 0 Then
            MsgBox "Negatives only, please try again!", vbCritical, "Aborting!"
            Application.EnableEvents = False
            Application.Undo
            Application.EnableEvents = True
        End If
    End If
End Sub

Select allOpen in new window

See attached.

However, I still fail to see how data validation doesn't work properly for you.  If you'd explain perhaps I can help with that as non-vba approaches can be less maintenance.

Dave
checkIfValid-r1.xls