CODEWITHSUNDEEP
excelvbasearch
narue1992
narue1992

Reputation: 1223

Search String in Spreadsheet and Highlight

I am using VBA in excel to use Sheet 2's list of string values and query Sheet 1's data to see if Sheet 2's values exist. If they exist, highlight the cells in Sheet1.

The listing I have in Sheet 2 is as follows:

COLUMN A

Admin Services

executives

firewall'

generator

internet

IT Application Support

proxy

remedy

My VBA is:

Private Sub CommandButton1_Click()

row_num = 1

Query = Sheet2.Range("A:A")

While Trim(ActiveCell.Value) <> ""

row_num = row_num + 1
    item_sum = Sheet1.Range("B" & row_num)
    item_note = Sheet1.Range("C" & row_num)
    item_group = Sheet1.Range("E" & row_num)

        If (InStr(item_sum, Query) Or InStr(item_note, Query) Or InStr(item_group, Query)) Then

            ActiveCell.Interior.Color = RGB(255, 255, 0)

        End If

Wend


End Sub

Right now I have no error flags and nothing highlights. I tried using Query value for Sheet 2's column A but I am not sure if it is working.

enter image description here

Update:

I tried changing the While loop to:

Do
DoEvents
row_num = row_num + 1
    item_sum = Sheet1.Range("B" & row_num)
    item_note = Sheet1.Range("C" & row_num)
    item_group = Sheet1.Range("E" & row_num)

        If (InStr(item_sum, Query) Or InStr(item_note, Query) Or InStr(item_group, Query)) Then

            ActiveCell.Interior.Color = RGB(255, 255, 0)

        End If

Loop Until item_sum = ""

but my highlighting did the following: enter image description here

Upvotes: 0

Views: 79

Answers (1)

Ralph
Ralph

Reputation: 9444

I think your code works just fine. The only problem is that you are not highlighting the cell which is being checked but the ActiveCell. So, you might want to consider changing that to something like this:

Option Base 0
Option Explicit
Option Compare Text

Public Sub CommandButton1_Click()
Dim item_sum, item_note, item_group As String
Dim lngRowNumber As Long
Dim varFound As Variant
Dim rngQuery As Range

Set rngQuery = Sheet2.Range("A:A")
lngRowNumber = 1

Do
    If Trim(item_sum) = vbNullString Then Exit Do
    ' Go to the next row
    lngRowNumber = lngRowNumber + 1
    ' Get the data to look for
    item_sum = Sheet1.Range("B" & lngRowNumber).Value2
    item_note = Sheet1.Range("C" & lngRowNumber).Value2
    item_group = Sheet1.Range("E" & lngRowNumber).Value2
    ' Check the item_sum
    Set varFound = rngQuery.Find(item_sum, LookIn:=xlValues, LookAt:=xlPart)
    If Not varFound Is Nothing Then
        Sheet1.Range("B" & lngRowNumber).Interior.Color = RGB(255, 255, 0)
        Set varFound = Nothing
    End If
    ' Check the item_note
    Set varFound = rngQuery.Find(item_note, LookIn:=xlValues, LookAt:=xlPart)
    If Not varFound Is Nothing Then
        Sheet1.Range("C" & lngRowNumber).Interior.Color = RGB(255, 255, 0)
        Set varFound = Nothing
    End If
    ' Check the item_group
    Set varFound = rngQuery.Find(item_group, LookIn:=xlValues, LookAt:=xlPart)
    If Not varFound Is Nothing Then
        Sheet1.Range("E" & lngRowNumber).Interior.Color = RGB(255, 255, 0)
        Set varFound = Nothing
    End If
Loop

End Sub

Obviously, I also made some minor changes to the overall code. Hope some of them are helpful.

Upvotes: 1

Related Questions