Pop-up Message Box when certain values are inputted in Column A

[asnjunior]

Everytime the values 1002, 1004, and 1005 are inputted in column A, I want the message to pop up "Reminder to indicate color". Below is the code that I have right now in the worksheet. As you can see this works when the value 1002 is inputted but when I tried to make it - If Cell.Value = "1002" Or "1004", the code doesn't work. The message pops up regardless of what the change is. How can I make the message pop up only on the values that I want? Thank you in advance!



Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim Cell As Range
If Not Intersect(Target, Columns("A")) Is Nothing Then
For Each Cell In Intersect(Target, Columns("A"))
If Cell.Value = "1002" Then MsgBox "Remember to indicate fur label color - red or blue."
Next
End If
End Sub

[Paul_Hossler]

If Cell.Value = "1002" Or Cell.Value = "1004"


Another way



Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)
Dim rCell As Range ' try not to use VBA or Excel keywords


If Intersect(Target, Columns("A")) Is Nothing Then Exit Sub

For Each rCell In Intersect(Target, Columns("A"))
Select Case rCell.Value
Case "1002", "1004"
MsgBox "Remember to indicate fur label color - red or blue."
End Select
Next
End Sub

[p45cal]

Since the loop is to cater for multiple cells in column A being changed at once, as in a copy paste or an entry into multiple cells by holding the Ctrl key while entering data, one can avoid the messsage popping up multiple times by adding Exit For directly after the MsgBox line in Paul's suggestion.

[asnjunior]

Thanks, Paul! It worked! Additional question though. Is it possible to check if the value of the cell belongs to a list (for example C1:C20) instead of explicitly indicating the values in the code ("1002", "1004")?

Thanks for the tip, p45cal.

[Paul_Hossler]

This is the I habitually do something like. Probably better ways


Not tested



Option Explicit




Private Sub Worksheet_Change(ByVal Target As Range)
Dim rCell As Range ' try not to use VBA or Excel keywords
Dim n As Long


If Intersect(Target, Columns("A")) Is Nothing Then Exit Sub

For Each rCell In Intersect(Target, Columns("A")).Cells
n = 0
On Error Resume Next
n = Application.WorksheetFunction.Match(rCell.Value, Worksheets("Sheet2").Range("C1:C20"), 0)
On Error GoTo 0

If n > 0 Then
MsgBox "Remember to indicate fur label color - red or blue. -- " & rCell.Address
End If
Next
End Sub

[asnjunior]

Thanks, Paul! It worked!