If, Case, and String Help

[Smoakstack]

I have data coming into a sheet via copy and paste. The thing is, i only want them to copy and paste a certain way. ie: If they sort there data wrong before copying and pasting, then I want a msgbox to come up and tell them they are doing it wrong. Here is the code I have:

Sub Model1()

Select Case Sheet2.Range("D4").Value
Case 1 To 1500

'what happens if it meets criteria

Case Else
Reply = MsgBox("Please sort data by area only.", vbOKOnly, Error)

Application.ScreenUpdating = False
'Unlocks Information
OSTExport20.Unprotect Password:=""
Sheet1.Unprotect Password:=""
Sheet2.Unprotect Password:=""
Sheet3.Unprotect Password:=""
Sheet4.Unprotect Password:=""
Sheet5.Unprotect Password:=""
Sheet6.Unprotect Password:=""
Sheet7.Unprotect Password:=""
Sheet8.Unprotect Password:=""
Sheet9.Unprotect Password:=""
Sheet10.Unprotect Password:=""
Worksheets("On-Center Output").Range("D3:N926").ClearContents
'Locks Information
OSTExport20.Protect Password:=""
Sheet1.Protect Password:=""
Sheet2.Protect Password:=""
Sheet3.Protect Password:=""
Sheet4.Protect Password:=""
Sheet5.Protect Password:=""
Sheet6.Protect Password:=""
Sheet7.Protect Password:=""
Sheet8.Protect Password:=""
Sheet9.Protect Password:=""
Sheet10.Protect Password:=""
Application.ScreenUpdating = True
Exit Sub
End Select
End Sub

It would be checked using a workbook change event. problem is, linking the two together.

Essentially I am checking so that if d4 is not a number from 1-1500, then to pop up a message box saying to sort it by area only. But I come up with errors.

D3 should have text and D4 should have a number between 1 and 1500. Im not sure what I am doing wrong? Any guidance would greatly help

 

[Smoakstack]

forgot other data.

If Target <> "" And Sheet2.Range("X1") = "" And Sheet2.Range("D4") < 1500 And Sheet2.Range("D4") > 0 Then

'do whats needed

Else: Call Model1


End If