Solved: Highlighting Rows based on a Text String Search

[mbake16]

Happy New Year:

In the attached you'll see a very novice macro, "Highlight_Vendors6()". The macro searches for text typed in an input box. For cells in which the text is found, that row, from column A through R, is Bolded and highlighted in yellow. I've used the Offset method to highlight the rows and am wondering if there is a simpler way to specify the row ranges to highlight as this method takes a lot of time when working with data sheets having several hundred rows or more to loop through.

Any help/thoughts you may have would be great!

Thank You!

mbake16

[Bob Phillips]

Sub Highlight_Vendors6()

Dim x As Long
Dim LastRow As Long
Dim vendorsearch As String

vendorsearch = InputBox("Enter Vendor to Highlight", "Highlight Vendor", "<Enter Vendor Here>")

LastRow = Range("B65536").End(xlUp).Row

On Error Resume Next

For x = LastRow To 5 Step -1

With Range("A" & x)

If .Offset(0, 1).Value = vendorsearch Then

.Resize(1, 18).Font.Bold = True
.Resize(1, 18).Interior.ColorIndex = 6
Else

.Resize(1, 18).Font.Bold = False
.Resize(1, 18).Interior.ColorIndex = xlColorIndexNone

End If
End With
Next x

End Sub

[mbake16]

Thank you so much for your help. That's a way to use the Offset method I wasn't aware of. I used the "Find" method in my original code because the end user will typically just type in part of the name to search on. I tried to create an If-Then statement using the Find method but can't figure out how to do it correctly. Would you know of a way to include the Find method in your code above vs. searching on the exact value of the text string? Thank you again for your help!

[Bob Phillips]

Sub Highlight_Vendors6()

Dim x As Long
Dim LastRow As Long
Dim vendorsearch As String

vendorsearch = InputBox("Enter Vendor to Highlight", "Highlight Vendor", "<Enter Vendor Here>")

LastRow = Range("B65536").End(xlUp).Row

On Error Resume Next

For x = LastRow To 5 Step -1

With Range("A" & x)

If .Offset(0, 1).Value Like "*" & vendorsearch & "*" Then

.Resize(1, 18).Font.Bold = True
.Resize(1, 18).Interior.ColorIndex = 6
Else

.Resize(1, 18).Font.Bold = False
.Resize(1, 18).Interior.ColorIndex = xlColorIndexNone

End If
End With
Next x

End Sub

[mbake16]

Awesome! Thank you very much! I'm really pushing it here but one quick question; is there a way to make the "Like" operator search in a non-case sensitive way?

You've already saved me countless hours of banging my head against the wall, very much appreciated!

[Bob Phillips]

Sub Highlight_Vendors6()

Dim x As Long
Dim LastRow As Long
Dim vendorsearch As String

vendorsearch = InputBox("Enter Vendor to Highlight", "Highlight Vendor", "<Enter Vendor Here>")

LastRow = Range("B65536").End(xlUp).Row

On Error Resume Next

For x = LastRow To 5 Step -1

With Range("A" & x)

If LCase(.Offset(0, 1).Value) Like "*" & LCase(vendorsearch) & "*" Then

.Resize(1, 18).Font.Bold = True
.Resize(1, 18).Interior.ColorIndex = 6
Else

.Resize(1, 18).Font.Bold = False
.Resize(1, 18).Interior.ColorIndex = xlColorIndexNone

End If
End With
Next x

End Sub

[mbake16]

THANK YOU ONCE AGAIN! This is extremely helpful.