首页 文章

在整个Excel工作簿中搜索文本字符串并突出显示单元格

提问于
浏览
-3

我需要搜索包含多个工作表的整个Excel workbook ,这些工作表可能存在于多个列中(例如,在范围列A到列J中)

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

这是可能的,还是我必须为每张纸制定一条规则?

一个例子:

  • 在我的 workbook 中找到字符串"information"并格式化单元格蓝色

我有多个不同的文本字符串要输入,每个文本字符串都有不同的颜色格式 .
有没有办法将它们组合在一个规则中,或者这只需要让我为每个规则创建一个新规则,使用为每个文本字符串修改的相同规则?

我对Excel中的条件格式非常陌生,所以如果你能够温和并指导我完成每一步,我将不胜感激 .

我搜索了oracle网络并找到了这个解决方案 . 我需要测试它,但它可能会做我需要的 .

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

码:

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

2 回答

  • 0

    我需要的是一个好的VBA解决方案......但是在这里你最好使用 Find + Replace (Ctrl H / Cmd H) . 单击"Options"按钮,在替换旁边,您可以格式化文本以替换它 . 选择"Format"然后填充蓝色 . 将"information"放在查找和替换字段中并运行:)

  • 0

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

    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范围内查找的值 .
    这些值的背景已根据需要进行着色,代码只是找到匹配的值并复制颜色 .

    enter image description here

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

    enter image description here

    然后你用 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
    

    enter image description here

相关问题