Solved: Msg Box code for a RANGE

[Bruce Gyger]

the following only works for E14. I want to see the MSG BOX upon the return of "1 EMT" for any cell within range E14:E200, but when I use "E14:E200" as Range, I get a Runtime Error 13: Type mismatch. Any ideas?

I should mention that I am already using Validation List for the values in this range, so that option is out.

[VBA]
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rng As Range
Set rng = Range("e14")
If Not Intersect(Target, rng) Is Nothing Then
If rng = ("1 EMT") Then
MsgBox "option for move-in/move-out only"
End If
End If
Set rng = Nothing
End Sub
[/VBA]

[Cyberdude]

Try omitting the parens from this If rng =("1 EMT")

[mdmackillop]

I think it is Target you want to check, not Rng.
[vba]Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rng As Range
Set rng = Range("E14:E200")
If Not Intersect(Target, rng) Is Nothing Then
If Target = "1 EMT" Then
MsgBox "option for move-in/move-out only"
End If
End If
Set rng = Nothing
End Sub [/vba]

[Bruce Gyger]

That did it. Target rather than rng. Thanks.

[YasserKhalil]

Try this
I added a line to avoid debug if you tried for example to clear the contents of more than one cell within the range(E14:E200)

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim rng As Range
    Set rng = Range("E14:E200")
    If Not Intersect(Target, rng) Is Nothing Then
        If Target.Count > 1 Then Exit Sub
        If Target = "1 EMT" Then
            MsgBox "Option for move-in/move-out only"
        End If
    End If
    Set Target = Nothing
End Sub

[Bruce Gyger]

Excellent. Thank you.

[mdmackillop]

Hi Yasser

If Target.Count > 1 Then Exit Sub

Best to place this right after the Dim statements to minimise the lines of code to be processed.

[YasserKhalil]

Nice tip Mr. mdmackillop
We're learning from you every day...