在电子表格中搜索字符串并突出显示

问题描述

我在excel中使用VBA来使用工作表2的字符串值列表，并查询工作表1的数据以查看工作表2的值是否存在.如果存在，请突出显示Sheet1中的单元格.

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.

我在工作表2中的清单如下:

The listing I have in Sheet 2 is as follows:

A栏

管理服务

主管

防火墙"

发电机

互联网

IT应用程序支持

代理

补救措施

我的VBA是:

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

现在我没有错误标志，也没有突出显示.我尝试对工作表2的A列使用查询值，但不确定它是否有效.

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.

更新:

我尝试将While循环更改为:

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:

推荐答案

我认为您的代码可以正常工作.唯一的问题是您没有突出显示正在检查的单元格，而是 ActiveCell .因此，您可能需要考虑将其更改为以下内容:

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.

这篇关于在电子表格中搜索字符串并突出显示的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！