Macro for selecting specifc rows of a worksheet

[boj]

I am interested in getting a macro that will find specific text in
column A of a spreadsheet and then highlight that row and the three
rows following, for every occurrence of the specific text so that these
rows can be copied into a new sheet.Just to be clear if the particular
text is found in column A in rows 10 then 75 then 150, I want rows
10-13, 75-78 and 150-153 all highlight so that they can be copied to
another sheet. Can anyone help.

 

[anonymousA]

for instance, if the text is "toto" and the range to search is
range("A1:A65536")

With Range("A1:A65536")
Set c = .Find(What:="toto", LookIn:=xlConstants, Lookat:=xlWhole)
'par défaut Lookat:=xlPart et c'est bien ce que l'on souhaite
If Not c Is Nothing Then
firstaddress = c.Address
Range(c, c.Offset(3, 0)).EntireRow.Interior.ColorIndex = 3
Set c = .FindNext(c)
Do While c.Address <> firstaddress And Not c Is Nothing
Range(c, c.Offset(3, 0)).EntireRow.Interior.ColorIndex = 3
Set c = .FindNext(c)
Loop
End If
End With

 

[boj]

I tried this solution but got a run time error '9' subscript out of
range. So i tried debug and the following line was highlighted yellow
Set c = .Find(What:="toto", LookIn:=xlConstants, Lookat:=xlWhole)

I am not sure what is wrong with that line could someone help.

 

[Gord Dibben]

Change "xlConstants" to "xlValues".........no quotes.


Gord Dibben MS Excel MVP

 

[boj]

The last post almost has me to where I want to be. The macro now
highlights the rows based on the criteria but what I really want is for
the macro to select the rows not highlight them. ie after the macro has
been run I want to execute the commany copy and paste into another
sheet the rows that the macro is now highlighting.