Highlght rows

[Alan M]

I have a column containing a collection of various numbers. I need to use
code to highlight the rows containing the number 5.

I have tried with this but it does not work.

For Each Cell In Columns(2)

If Cell.Value = "5" Then
EntireRow.Select

With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With
End If

Not sure why....Help please?

 

[JE McGimpsey]

It's probably not working because your comparison looks for text and
your cells contain numbers.

Try:

Dim rCell As Range
For Each rCell In Range("B1:B" & _
Range("B" & Rows.Count).End(xlUp).Row)
With rCell
If IsNumeric(.Value) Then
If .Value = 5 Then
With .EntireRow.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With
End If
End If
End With
Next rCell

Note that there's no need to select anything.

 

[john]

this may work but assumes no blank rows in range you are checking:

For Each rw In Worksheets(1).Cells(1, 1).CurrentRegion.Rows
check = rw.Cells(1, 2).Value
If check = 5 Then rw.EntireRow.Interior.ColorIndex = 15
Next

 

[Don Guillett]

sub colorrows()
mc=2
for i=1 to cells(rows.count,mc).end(xlup).row
if cells(i,mc)=5 then rows(i).interior.colorindex=15
next i
end sub