I am trying to drastically shorten my vba code and unfortunately my inexperience in vba means that I cannot translate other examples i've found to suit my own, hopefully someone can help;
I have 20 cells (D19 to D38) that must be filled in with any of the following text, "M", "M2", "M3", "TON" or "EACH" I therefore need a message box that pops up and tells the user if they have not entered an exact match.
I know how to do this with 1 cell but I am trying to find a way to make it apply to all 20 cells,
my code is written for my 1st cell (D19) only;
Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet
Set ws = Worksheets("JobSummary")
S1 = "M"
S2 = "M2"
S3 = "M3"
S4 = "TON"
S5 = "EACH"
If ws.Range("D19") = S1 Or ws.Range("D19") = S2 Or ws.Range("D19") = S3 Or ws.Range("D19") = S4 Or ws.Range("D19") = S5 Then
Exit Sub
Else
ws.Range("D19").Select
MsgBox "Unit of measure does not match M, M2, M3, TON or EACH exactly, check spelling and/or case sensitivity"
End If
End Sub
hope someone can help.
Bookmarks