Searching for a string and then shading the row
 

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!

Searching for a string and then shading the row
 

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


--
Hope that helps.

Vergel Adriano


"Greg Spencer" wrote:

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!

Searching for a string and then shading the row
 

That works great. Thanks!

Greg

"Vergel Adriano" wrote:

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


--
Hope that helps.

Vergel Adriano


"Greg Spencer" wrote:

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!