Searching for a string and then shading the row

[Guest]

Being new to VBA programming, I'm struggling to put together a macro that
will accomplish the following:

1. Search the cells in a specific column (H) - starting at row 3 - for the
presence of a particular word from a list of 3 different words.

2. If one of the words is found, the entire row (from columns A through H)
would be shaded.

3. The search would continue through the rest of the cells in the column
with the same action being repeated to the end of the column.

All help would be appreciated!

 

[Guest]

Greg,

Here's one way to do it:

Sub test()
Dim rng As Range
Dim c As Range
Dim vWords As Variant

vWords = Array("One", "Two", "Three")

Set rng = Range(Range("H3"), Range("H65535").End(xlUp))

For Each c In rng
If InStr(1, c.Text, vWords(0), vbTextCompare) > 0 Or _
InStr(1, c.Text, vWords(1), vbTextCompare) > 0 Or _
InStr(1, c.Text, vWords(2), vbTextCompare) > 0 Then

'color it gray
c.Offset(0, -7).Resize(1, 8).Interior.ColorIndex = 15

End If

Next c
End Sub

 

[Guest]

That works great. Thanks!

Greg

Greg,

Here's one way to do it:

Sub test()
Dim rng As Range
Dim c As Range
Dim vWords As Variant

vWords = Array("One", "Two", "Three")

Set rng = Range(Range("H3"), Range("H65535").End(xlUp))

For Each c In rng
If InStr(1, c.Text, vWords(0), vbTextCompare) > 0 Or _
InStr(1, c.Text, vWords(1), vbTextCompare) > 0 Or _
InStr(1, c.Text, vWords(2), vbTextCompare) > 0 Then

'color it gray
c.Offset(0, -7).Resize(1, 8).Interior.ColorIndex = 15

End If

Next c
End Sub