VBA Alert if Cell Contains Text

[ShawnExcel]

I have the code to send a CDO e-mail, but I'm having trouble with the following code to find a certain word within text being pulled off the internet. Essentially what I'm looking for is a trigger, any time a cell is changed, where it looks for any of the words listed in column D, in column B. Column B will update every 5 minutes from the internet. The words "Chicken" and "Beef" and "Fish" are in column D, so if any of those three words appear in a column D update, I want the code to call the e-mail function and automatically send me an alert. I feel like this should be easy but I'm struggling and you guys always come up with more elegant solutions than I ever could.

Thank you so much!

 

[Stevie]

Hiya Shawn,
try this:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim inputRange, checkRange As Range
    Dim strLoc As Integer
    Dim checkString As String
    Set checkRange = Range("D1:D10")
    Set inputRange = Range("B1:B1000")
    If Not Application.Intersect(inputRange, Range(Target.Address)) Is Nothing Then
        For Each cell In checkRange.Cells
            checkString = checkString & "|" & cell.Text
        Next
        For Each cell In inputRange.Cells
            If cell.Text <> "" Then
                strLoc = InStr(checkString, cell.Text)
                If strLoc <> 0 Then
                    MsgBox "Cell " & cell.Address & " contains a keyword."
                    'change msgbox line above to your code to send the email
                End If
            End If
        Next
    End If
End Sub

you can change the ranges to be different, I have set your keywords range to be D1:D10, and your input range to be B1:B1000, but these can be anything.

Edit: Oh, I should mention, this needs to go in the code module for the sheet which contains your data.
Edit2: I noticed an ordering issue in the code, fixed.

If it works and you like the response, please click like