Hi-light area based on name found in column A
 

How would I highlight an area in a spreadsheet based on a name found in
column A?

such as ... if the name frog was found in column A .. how would I highlight
through a macro ... col C, D, and E all the rows that have Frog listed in
Column A?

Hi-light area based on name found in column A
 

Use conditional formatting.

Set up your formula to look at the entry in column A

Select column C,D,E with C1 as the active cell

Under format=Conditional formatting change Cell Value is to Formula is
using the dropdown. In the text box put in the formula

=$A1="frog"

then click format and choose the formatting you want.

--
Regards,
Tom Ogilvy


"Annette" wrote in message
...
How would I highlight an area in a spreadsheet based on a name found in
column A?

such as ... if the name frog was found in column A .. how would I
highlight
through a macro ... col C, D, and E all the rows that have Frog listed in
Column A?

Hi-light area based on name found in column A
 

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

- Piku

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

Hi-light area based on name found in column A
 

But how would I write this into a macro or write the macro for this?

"Tom Ogilvy" wrote in message
...
Use conditional formatting.

Set up your formula to look at the entry in column A

Select column C,D,E with C1 as the active cell

Under format=Conditional formatting change Cell Value is to Formula is
using the dropdown. In the text box put in the formula

=$A1="frog"

then click format and choose the formatting you want.

--
Regards,
Tom Ogilvy


"Annette" wrote in message
...
How would I highlight an area in a spreadsheet based on a name found in
column A?

such as ... if the name frog was found in column A .. how would I
highlight
through a macro ... col C, D, and E all the rows that have Frog listed
in
Column A?

Hi-light area based on name found in column A
 

Turn on the macro recorder while you do it manually.

--
Regards,
Tom Ogilvy

"Annette" wrote in message
...
But how would I write this into a macro or write the macro for this?

"Tom Ogilvy" wrote in message
...
Use conditional formatting.

Set up your formula to look at the entry in column A

Select column C,D,E with C1 as the active cell

Under format=Conditional formatting change Cell Value is to Formula is
using the dropdown. In the text box put in the formula

=$A1="frog"

then click format and choose the formatting you want.

--
Regards,
Tom Ogilvy


"Annette" wrote in message
...
How would I highlight an area in a spreadsheet based on a name found
in
column A?

such as ... if the name frog was found in column A .. how would I
highlight
through a macro ... col C, D, and E all the rows that have Frog listed
in
Column A?

Hi-light area based on name found in column A
 

If you are going to color the whole row, why repeat the same command on only
columns C:E?

Worksheets("Sheet1").Rows(x).Interior.ColorInde x = 6
Worksheets("Sheet1").Columns("C:E").Interior.Colo rIndex = 6

While probably not a big deal, you correctly calculate the last row of the
usedrange using a formula that recognizes it doesn't necessarily start in
row 1, then use a hard coded 1 as the start row for your loop.

lr = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count
? lr
21
? Activesheet.UsedRange.rows.count
11

In this case, the usedRange didn't start with row 1.

--
Regards,
Tom Ogilvy


"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/

Hi-light area based on name found in column A
 

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/

Hi-light area based on name found in column A
 

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/

Hi-light area based on name found in column A
 

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/

Hi-light area based on name found in column A
 

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/

Hi-light area based on name found in column A
 

Yes .. this is it ... thank you thank you thank ... I really try to write
the correct terminology but I failed miserably here. Once you understood
what I needed, you provided the perfect answer. Thank you!


"Tom Ogilvy" wrote in message
...
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/