Reputation: 1203
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.
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:
Upvotes: 0
Views: 79
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