Hi-light area based on name found in column A

  • Thread starter Thread starter Annette
  • Start date Start date
A

Annette

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?
 
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.
 
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.ColorIndex = 6
End If
Next x

- Piku
 
If you are going to color the whole row, why repeat the same command on only
columns C:E?
Worksheets("Sheet1").Rows(x).Interior.ColorIndex = 6
Worksheets("Sheet1").Columns("C:E").Interior.ColorIndex = 6
Click to expand...

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.
 
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
 
I don't want to select the entire columns

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

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

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

--
Regards,
Tom Ogilvy

Annette said:
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
Click to expand...
 
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 said:
I don't want to select the entire columns
Click to expand...

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

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

rather than
Click to expand...
 
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
 
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!
 
Back
Top