I guess Hilight was interpreted as coloring the cell.

originally you said C to E


Sub hilit()
Dim rng as Range, x as long, lr as long
lr = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count
For x = 1 To lr
If Worksheets("Sheet2").Cells(x, 1).Value = "Frog" Then
if rng is nothing then
set rng = Worsheets("sheet2").Cells(x,4).Resize(1,2)
else
set rng = Union(rng,Worksheets("Sheet2").Cells(x,4).Resize(1 ,2))
End if
End If
Next x
if not rng is nothing then
rng.Select
End if
End Sub


--
Regards,
Tom Ogilvy


"Annette" wrote in message
...
Okay, I'm not understanding this so let me just explain one more time. I
want to find every row in column A that contains the word "Frog" ... and
select every row in col D and E that correspond to this.

col a d e
toad 1 2
toad 1 5
frog 1 5
frog 3 7
frog 2 5

So my result will be the row 3, 4, and 5 with col D and E of those row
selected. The macro is only selecting the last row.

Thanks!



"Tom Ogilvy" wrote in message
...
I don't want to select the entire columns

so why right code that does that:
Worksheets("Sheet2").Columns("C:E").Select

why not
worksheets("Sheet2").Cells(x,3).Resize(1,3).Interi or.ColorIndex = 6

rather than
Worksheets("Sheet2").Rows(x).Select
Worksheets("Sheet2").Columns("C:E").Select

--
Regards,
Tom Ogilvy

"Annette" wrote in message
...
Getting closer ... I modified this a bit to fit my needs, but now ...
here's
the problem, I don't want to select the entire columns, rather ...
just
those rows that match the Frog: Here's what I did to modify:

Sub hilit()
lr = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count
For x = 1 To lr
If Worksheets("Sheet2").Cells(x, 1).Value = "Frog" Then
Worksheets("Sheet2").Rows(x).Select
Worksheets("Sheet2").Columns("C:E").Select
End If
Next x
End Sub

"pikus " wrote in message
...
If I understand your question correctly then.

lr = ActiveSheet.UsedRange.Row - 1 +
ActiveSheet.UsedRange.Rows.Count
For x = 1 To lr
If Worksheets("Sheet1").Cells(x, 1).Value = "Frog" Then
Worksheets("Sheet1").Rows(x).Interior.ColorIndex = 6
Worksheets("Sheet1").Columns("C:E").Interior.Color Index = 6
End If
Next x

- Pikus


---
Message posted from http://www.ExcelForum.com/