Here is my plan:
For Column A, if cell value > 1, prompt MsgBox, then go to check Column B.
For Column B, if cell value > 2, prompt MsgBox, then go to check Column C.
For Column C, if cell value > 20, prompt MsgBox, End Sub
To test this code:
First of all, I insert value of 0, 1, 21 into any blank cell in the worksheet. (Eg: D2, E2, F2)
Then I copy and paste value of D2, E2, F2 into A2, B2, C2.
However, it repeats the Column A & B Msgbox although the values are not exceeded.
Byright, it should only prompt Column C MsgBox since the value is >20.
The code is working perfectly if I key in value one by one into A2, B2, C2.
But it is not working for copy and paste method.
Somehow it will repeats previous column MsgBox.
Please help me find errors in my code. Thanks for helping!
Here is my code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim aCell As Range
Dim bCell As Range
Dim cCell As Range
Set aCell = Range("A2:A10")
Set bCell = Range("B2:B10")
Set cCell = Range("C2:C10")
On Error GoTo Whoa
Application.EnableEvents = False
If Not Intersect(Target, aCell) Is Nothing Then
For Each aCell In Target
If aCell.Value > 1 Then
If MsgBox("A Temperature Exceeded", vbExclamation, "Warning") = vbOK Then GoTo Line1
End If
Next
End If
Line1:
If Not Intersect(Target, bCell) Is Nothing Then
For Each bCell In Target
If bCell.Value > 2 Then
If MsgBox("B Temperature Exceeded", vbExclamation, "Warning") = vbOK Then GoTo Line2
End If
Next
End If
Line2:
If Not Intersect(Target, cCell) Is Nothing Then
For Each cCell In Target
If cCell.Value > 20 Then
If MsgBox("C Temperature Exceeded", vbExclamation, "Warning") = vbOK Then
End If
End If
Next
End If
Letscontinue:
Application.EnableEvents = True
Exit Sub
Whoa:
MsgBox Err.Description
Resume Letscontinue
End Sub
Bookmarks