Tweaking an Excel 2003 macro

The following macro ("Test") is supposed to search worksheet 1 (sample attached) for specified text terms, copy the rows that contains the search term to worksheet 2, and finally highlight the cells which contained the search term in worksheet 1.
1) I would like to modify the macro to have it search only for terms in column J.
2) Excel 2003 bug? When the search term is not within the first 36 words of text, the macro highlights the cell but does not copy the row; later rows containing the search term within the 1st 36 words are copied but not highlighted. Try the macro with the term "demonstrate" or "stain" (both appear in J2 after 36 words and in J8 within 36 words). The macro highlights J2 only, but copies row 8 only.

Option Explicit
Sub Test()
Dim myWord$
myWord = InputBox("What key word to copy rows", "Enter your word")
If myWord = "" Then Exit Sub

Application.ScreenUpdating = False
Dim xRow&, NextRow&, LastRow&
NextRow = 2
LastRow = Cells.Find(What:="*", After:=Range("A1"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

For xRow = 1 To LastRow
If WorksheetFunction.CountIf(Rows(xRow), "*" & myWord & "*") > 0 Then

Cells.Find(What:=(myWord), After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False).Activate

With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With

Rows(xRow).Copy Sheets("Sheet2").Rows(NextRow)
NextRow = NextRow + 1
End If
Next xRow

Application.ScreenUpdating = True

MsgBox "Macro is complete, " & NextRow - 2 & " rows containing" & vbCrLf & _
"''" & myWord & "''" & " were copied to Sheet2.", 64, "Done"
End Sub

Please Login or Register  to view this content.