Try this...
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 Intersect(Target, aCell)
If aCell.Value > 1 Then
If MsgBox("A Temperature Exceeded", vbExclamation, "Warning") = vbOK Then Exit For
End If
Next
End If
If Not Intersect(Target, bCell) Is Nothing Then
For Each bCell In Intersect(Target, bCell)
If bCell.Value > 2 Then
If MsgBox("B Temperature Exceeded", vbExclamation, "Warning") = vbOK Then Exit For
End If
Next
End If
If Not Intersect(Target, cCell) Is Nothing Then
For Each cCell In Intersect(Target, cCell)
If cCell.Value > 20 Then
If MsgBox("C Temperature Exceeded", vbExclamation, "Warning") = vbOK Then Exit For
End If
Next
End If
Letscontinue:
Application.EnableEvents = True
Exit Sub
Whoa:
MsgBox Err.Description
Resume Letscontinue
End Sub
Or this...
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
On Error GoTo Whoa
If Not Intersect(Target, Range("A2:C10")) Is Nothing Then
For Each cell In Intersect(Target, Range("A2:C10"))
Select Case cell.Column
Case 1: If cell.Value > 1 Then MsgBox "A Temperature Exceeded", vbExclamation, "Warning"
Case 2: If cell.Value > 2 Then MsgBox "B Temperature Exceeded", vbExclamation, "Warning"
Case 3: If cell.Value > 20 Then MsgBox "C Temperature Exceeded", vbExclamation, "Warning"
End Select
Next
End If
Letscontinue:
Exit Sub
Whoa:
MsgBox Err.Description
Resume Letscontinue
End Sub
Bookmarks