Hi Howard,

Am Sun, 22 Jun 2014 09:22:59 +0200 schrieb Claus Busch:

If InStr(LCase(arrCheck(i, n)), MyArr(j)) Then

if you want a MsgBox if there are no matches then try:

Sub MyBadFoodFind3()

Dim i As Long, j As Long, n As Long
Dim MyArr As Variant, arrCheck As Variant
Dim LRow As Long, LCol As Long
Dim iRet As Integer
Dim strPrompt As String
Dim strTitle As String, myStr As String

With Sheets("Sheet1")
.Cells.Interior.ColorIndex = xlNone

strPrompt = " Highlights have been removed." & vbCr & _
"If you want to continue click ""Yes."""

strTitle = "My Bad Eats"

iRet = MsgBox(strPrompt, vbYesNo, strTitle)

If iRet = vbNo Then
Exit Sub
Else
'
End If

MyArr = Array("milk", "soda", "fries", "pizza", "beer", "chips", _
"candy", "alcohol", "mcdonalds", "wendys", "burger king")

arrCheck = .UsedRange
LCol = .Cells(1, Columns.Count).End(xlToLeft).Column
LRow = .Cells(Rows.Count, 1).End(xlUp).Row

Application.ScreenUpdating = False

For j = LBound(MyArr) To UBound(MyArr)
If WorksheetFunction.CountIf(.UsedRange, "*" & MyArr(j) & "*") =
0 Then
myStr = myStr & MyArr(j) & Chr(10)
GoTo NextLoop
Else
For i = 1 To LRow
For n = 1 To LCol
If InStr(LCase(arrCheck(i, n)), MyArr(j)) Then
.Cells(i, n).Interior.ColorIndex = 6
End If
Next n
Next i
End If
NextLoop:
Next j
End With

Application.ScreenUpdating = True
MsgBox "No matches found for:" & Chr(10) & myStr

End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional