I am using this code to receive an error message every time when in column "W" a text is inserted. When this happens the text is deleted and a box message appears:"The row W" & r & " must contain only digits!" which tells the row number of the error. r - is set as Target.Row
My problem is that, when I copy a text in the range w10:w12, I receive the error message 3 times, which is great. But, in the message box it shows only row number w10 - 3 times i.e."The row W10 must contain only digits!" . How can I make the code to show the message box with w10, then w11 and lastly then w12?
Private Sub Worksheet_Change(ByVal Target As Range) Dim cell As Range
Dim r As Long
r = Target.Row
Application.EnableEvents = False
For Each cell In Target
If Not Application.Intersect(cell, Range("w10:w10000")) Is Nothing Then
If Not IsNumeric(cell.Value) Then
MsgBox "The row W" & r & " must contain only digits!"
cell.Value = vbNullString
End If
End If
Next cell
Application.EnableEvents = True
Private Sub Worksheet_Change(ByVal Target As Range) Dim cell As Range
Application.EnableEvents = False
For Each cell In Target
If Not Application.Intersect(cell, Range("w10:w10000")) Is Nothing Then
If Not IsNumeric(cell.Value) Then
MsgBox "The row W" & cell.row & " must contain only digits!"
cell.Value = vbNullString
End If
End If
Next cell
Application.EnableEvents = True