VBA for Pop Up Message with Condition

[rhmkrmi]

Hi There!

I need a macro to generate a pop-up message when a condition is met.

Thanks!

 

[footoo]

https://www.google.com/search?q=VBA...69i57j69i60.4750j0j8&sourceid=chrome&ie=UTF-8

 

[My Aswer Is This]

If you were to provide some specific details I may be able to help you.

 

[rhmkrmi]

Thanks.

I am trying to say if any text like *LAB* is found in H:H and M:M, then generate a warning message reading "Enter Rate".

Thanks again.

 

[My Aswer Is This]

So assuming you want this script to run when *LAB* is entered into column M or Column H

So if LAB dog is entered or LAB Technician is entered into Column M or Column H a message box will pop up

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  1/15/2019  10:28:47 PM  EST
If Target.Column = 8 Or Target.Column = 13 Then
If InStr(Target.Value, "LAB") Then MsgBox "Enter Rate"
End If
End Sub

 

[rhmkrmi]

Perfect.
Thank you.

How can I define ranges instead of Target.Column = 8 Or ....

Thank you

 

[My Aswer Is This]

Try this using named Range "Cake"

Modify as needed

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  1/15/2019  11:16:47 PM  EST
If Not Intersect(Target, Range("Cake")) Is Nothing Then
If InStr(Target.Value, "LAB") Then MsgBox "Enter Rate"
End If
End Sub

 

[rhmkrmi]

Thanks.

Why does it give me the Run-time error 13 when I select any of those columns to past or clear data?
It does not give me that error when I update cell by cell though.

 

[My Aswer Is This]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  1/15/2019  11:46:47 PM  EST
If Not Intersect(Target, Range("Cake")) Is Nothing Then
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
If InStr(Target.Value, "LAB") Then MsgBox "Enter Rate"
End If
End Sub

 

[footoo]

Private Sub xWorksheet_Change(ByVal Target As Range)
Dim rng As Range, cel As Range
Set rng = Intersect(Target, [H:H,M:M])
If Not rng Is Nothing Then
    For Each cel In rng
        If InStr(cel, "LAB") Then
            MsgBox "Enter Rate"
            Exit For
        End If
Next
End If
End Sub