On Sunday, June 22, 2014 4:19:00 AM UTC-7, Claus Busch wrote:
Hi Howard,



Am Sun, 22 Jun 2014 03:53:03 -0700 (PDT) schrieb L. Howard:



I just so happened I had dispersed some sample test strings on the sheet all of which are with in the range od D4:H22.



then set your range and the start cell explicit:


Thar works fine.
You are a champion! Nice and thanks.

Regards,
Howard

Hi Howard,

Am Sun, 22 Jun 2014 04:27:39 -0700 (PDT) schrieb L. Howard:

Thar works fine.

to make the code a bit more universal you can also work with UsedRange:

Sub MyBadFoodFind4()

Dim i As Long, j As Long, n As Long
Dim MyArr As Variant, arrCheck As Variant, arrRng As Variant
Dim LRow As Long, LCol As Long
Dim iRet As Integer
Dim strPrompt As String, strRng As String
Dim strTitle As String, myStr As String
Dim StartC As Range
Dim OffRow As Long, OffCol As Long

With Sheets("Sheet1")
.UsedRange.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")

strRng = .UsedRange.Address(0, 0)
arrRng = Split(strRng, ":")
Set StartC = .Range(arrRng(0))
OffRow = StartC.Row - 1
OffCol = StartC.Column - 1

arrCheck = .UsedRange
LCol = .UsedRange.Columns.Count
LRow = .UsedRange.Rows.Count

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 + OffRow, n +
OffCol).Interior.ColorIndex = 6
End If
Next n
Next i
End If
NextLoop:
Next j
End With

Application.ScreenUpdating = True
If Len(myStr) 0 Then
MsgBox "No matches found for:" & Chr(10) & myStr
End If

End Sub


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

On Sunday, June 22, 2014 5:02:52 AM UTC-7, Claus Busch wrote:
Hi Howard,



Am Sun, 22 Jun 2014 04:27:39 -0700 (PDT) schrieb L. Howard:



Thar works fine.



to make the code a bit more universal you can also work with UsedRange:


Regards

Claus B.

--


Great, now one can either set the 'search' area to exclude certain cells using code ...3() or go with UsedRange for all cells using code ...4().

Thanks again Claus.

Regards,
Howard