What's the easiest way to search a worksheet for certain text in a column
and if found, change the color of the row (highlight)?

Thanks!


--- Posted via news://freenews.netfront.net/ - Complaints to ---

Since you are in the programming group I will assume you want a macro.

Sub color_rows()
Dim RngCol As Range
Dim i As Range
Set RngCol = Range("B1", Range("B" & Rows.Count). _
End(xlUp).Address)
For Each i In RngCol
If i.Value = "mystring" Then
i.EntireRow.Interior.ColorIndex = 3
End If
Next i
End Sub

There are ways to do it without VBA...............see help on
Conditional Formatting.


Gord Dibben Microsoft Excel MVP

On Fri, 22 Jul 2011 08:55:12 -0400, "Saucer Man"
wrote:

What's the easiest way to search a worksheet for certain text in a column
and if found, change the color of the row (highlight)?

Thanks!


--- Posted via news://freenews.netfront.net/ - Complaints to ---

On Jul 22, 2:04*pm, Gord wrote:
Since you are in the programming group I will assume you want a macro.

Sub color_rows()
Dim RngCol As Range
Dim i As Range
* * Set RngCol = Range("B1", Range("B" & Rows.Count). _
* * * * * * End(xlUp).Address)
* * For Each i In RngCol
* * * * If i.Value = "mystring" Then
* * * * * *i.EntireRow.Interior.ColorIndex = 3
* * * * End If
* * Next i
End Sub

There are ways to do it without VBA...............see help on
Conditional Formatting.

Gord Dibben * *Microsoft Excel MVP

On Fri, 22 Jul 2011 08:55:12 -0400, "Saucer Man"







wrote:
What's the easiest way to search a worksheet for certain text in a column
and if found, change the color of the row (highlight)?

Thanks!

--- Posted via news://freenews.netfront.net/ - Complaints to ---
How about filteringcoloringunfiltering
=
option explicit
Sub FilterAndColorRow()
Dim myvalue As String
Dim lr As Long
myvalue = InputBox("Enter text to find")
Cells.Interior.ColorIndex = xlNone
lr = Cells(Rows.Count, "a").End(xlUp).Row
Range("A1:a" & lr).AutoFilter Field:=1, _
Criteria1:=myvalue

Rows(2).Resize(lr - 1) _
.SpecialCells(xlCellTypeVisible). _
Interior.ColorIndex = 6

Range("a1:a" & lr).AutoFilter
End Sub

Thank you very much. Conditional formatting is another option that I didn't
think of when I sent this post.



--- Posted via news://freenews.netfront.net/ - Complaints to ---