Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Searching csv files for particular string | Excel Programming | |||
Searching for string in other cells | Excel Worksheet Functions | |||
searching through a string? | Excel Programming | |||
Searching a String | Excel Programming | |||
searching a string with a partial string | Excel Programming |