VBA Search worksheet, find text, change color of row

[Saucer Man]

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!

 

[Gord]

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

 

[Don Guillett]

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

How about filtering>coloring>unfiltering
=
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

 

[Saucer Man]

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