在整个Excel工作簿中搜索文本字符串并突出显示单元格 [英] search entire Excel workbook for text string and highlight cell

问题描述

我需要搜索包含多个工作表的整个Excel workbook 可能出现在多列中的文本字符串 (例如，在A列到J列的范围内)

I need to search an entire Excel workbook containing multiple sheets for text strings that may be present in multiple columns (say in the range column A to column J)

找到文本字符串后，它将颜色格式应用于单元格.

When a text string is found, it applies a color format to the cell.

这可能吗，还是我必须为每张纸制定一个规则?

Is this possible, or will I have to make a rule for each sheet?

一个例子:

• 在我的workbook中的任意位置查找字符串信息"，并格式化蓝色单元格

• Find string "information" anywhere in my workbook and format cell blue

我有多个不同的文本字符串可输入，每个文本字符串将具有不同的颜色格式.
有没有办法将它们全部组合成一条规则，还是只需要让我为每个文本创建一个新规则，并使用为每个文本字符串修改的相同规则?

I have multiple different text strings to enter and each will have a different color format.
Is there a way to combine them all in one rule, or will this just need to have me create a new rule for each, using the same rule modified for each text string?

我真的很擅长Excel中的条件格式设置，因此，如果您能礼貌地引导我完成将不胜感激的每一步.

I'm really new at the conditional formatting in Excel, so if you could be gentle and walk me through each step that would be appreciated.

我搜索了oracle互联网并找到了该解决方案.我需要对其进行测试，但它可能会满足我的要求.

I have searched the oracle internet and found this solution. I need to test it but it may do what I need.

这需要我建立一个名为ChooseColors的表.第一列是搜索字符串，第二列是颜色-从可用范围中选择. 搜索区域在第二张纸上-从此纸上开始.

This requires me to build a table called ChooseColors. The first column is the search string, the second is the colors -- pick from the available range. The search area is on a second sheet -- start on this sheet.

代码:

Sub DoColors()
Dim Picker As Variant
Dim Colors As Variant
Dim Rws As Long, j As Long
Dim i As Integer
Dim Sht As String
Dim c As Range
Dim FirstAddress

Sht = ActiveSheet.Name
'load search strings and colors into arrays
Application.Goto Reference:="ChooseColors"
ReDim Picker(1 To Selection.Rows.Count)
ReDim Colors(1 To Selection.Rows.Count)
For i = 1 To Selection.Rows.Count
Picker(i) = ActiveCell.Value
Colors(i) = ActiveCell.Offset(0, 1).Interior.ColorIndex
ActiveCell.Offset(1, 0).Select
Next i
'search the test range, changing backgrounds as required
Sheets(Sht).Activate
For i = 1 To UBound(Picker)
With Cells.SpecialCells(xlCellTypeConstants, xlTextValues)
    Set c = .Find(Picker(i), LookIn:=xlValues)
    If Not c Is Nothing Then
        FirstAddress = c.Address
        Do
            c.Interior.ColorIndex = Colors(i)
            Set c = .FindNext(c)
        Loop While Not c Is Nothing And c.Address <> FirstAddress
    End If
End With
Next i

End Sub

推荐答案

此代码基于您发布的第一组代码，将突出显示您在工作簿中键入的所有文本.

This code, based on the first set of code you posted, will highlight all occurrences of whatever text you type in within the workbook.

Public Sub find_highlight()

    'Put Option Explicit at the top of the module and
    'Declare your variables.
    Dim FindString As String
    Dim wrkSht As Worksheet
    Dim FoundCell As Range
    Dim FirstAddress As String

    FindString = InputBox("Information")

    'Use For...Each to cycle through the Worksheets collection.
    For Each wrkSht In ThisWorkbook.Worksheets
        'Find the first instance on the sheet.
        Set FoundCell = wrkSht.Cells.Find( _
            What:=FindString, _
            After:=wrkSht.Range("A1"), _
            LookIn:=xlValues, _
            LookAt:=xlWhole, _
            SearchOrder:=xlByRows, _
            SearchDirection:=xlNext, _
            MatchCase:=False)
        'Check it found something.
        If Not FoundCell Is Nothing Then
            'Save the first address as FIND loops around to the start
            'when it can't find any more.
            FirstAddress = FoundCell.Address
            Do
                With FoundCell.Interior
                    .ColorIndex = 6
                    .Pattern = xlSolid
                    .PatternColorIndex = xlAutomatic
                End With
                'Look for the next instance on the same sheet.
                Set FoundCell = wrkSht.Cells.FindNext(FoundCell)
            Loop While FoundCell.Address <> FirstAddress
        End If

    Next wrkSht

End Sub

---

要查找多个值和格式，可以使用以下代码.
它依赖于我称为Info的工作表，该工作表具有要在A1:A3范围内查找的值.
这些值的背景已根据需要进行了着色，并且代码仅查找匹配的值并在上面复制颜色.

---

To find more than one value and format you could use the following code.
It relies on a sheet I've called Info which has the values to look for in the range A1:A3.
The background to these values have been coloured as you want and the code just finds the matching values and copies the colour over.

您可以添加额外的代码以允许更多值，或使用动态命名范围返回源值.
动态命名范围将包含一个公式，例如:=Info!$A$1:INDEX(Info!$A:$A,COUNTA(Info!$A:$A))，其给定名称为'SourceValues'.
在Formula功能区上选择Define Name，然后将公式粘贴到Refers To:框中，并将SourceValues粘贴到名称中.

然后，您可以使用Set Information = Range("SourceValues")

You could add extra code to allow for more values, or use a dynamic named range to return your source values.
A dynamic named range would consist of a formula such as: =Info!$A$1:INDEX(Info!$A:$A,COUNTA(Info!$A:$A)) with a given name of 'SourceValues'.
Select Define Name on the Formula ribbon and paste the formula into the Refers To: box and the SourceValues into the name.

You'd then refer to the range with Set Information = Range("SourceValues")

Public Sub find_highlight()

    'Put Option Explicit at the top of the module and
    'Declare your variables.
    Dim FindString As String
    Dim wrkSht As Worksheet
    Dim FoundCell As Range
    Dim FirstAddress As String
    Dim InfoBit As Range
    Dim Information As Range

    Set Information = Range("SourceValues")
    'Set Information = ThisWorkbook.Worksheets("Info").Range("A1:A3")

    'Use For...Each to cycle through the information we're looking for.
    For Each InfoBit In Information
        'Use For...Each to cycle through the Worksheets collection.
        For Each wrkSht In ThisWorkbook.Worksheets
            'Ignore the "Info" sheet as it holds our values to search for.
            If wrkSht.Name <> "Info" Then
                'Find the first instance on the sheet.
                Set FoundCell = wrkSht.Cells.Find( _
                    What:=InfoBit, _
                    After:=wrkSht.Range("A1"), _
                    LookIn:=xlValues, _
                    LookAt:=xlWhole, _
                    SearchOrder:=xlByRows, _
                    SearchDirection:=xlNext, _
                    MatchCase:=False)
                'Check it found something.
                If Not FoundCell Is Nothing Then
                    'Save the first address as FIND loops around to the start
                    'when it can't find any more.
                    FirstAddress = FoundCell.Address
                    Do
                        'Copy all formatting - bit of screen flicker.
'                        InfoBit.Copy
'                        FoundCell.PasteSpecial Paste:=xlPasteFormats

                        'Just copy the Interior colour.
                        FoundCell.Interior.Color = InfoBit.Interior.Color

                        'Look for the next instance on the same sheet.
                        Set FoundCell = wrkSht.Cells.FindNext(FoundCell)
                    Loop While FoundCell.Address <> FirstAddress
                End If
            End If
        Next wrkSht
    Next InfoBit

End Sub

这篇关于在整个Excel工作簿中搜索文本字符串并突出显示单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！