excel 突出显示区域中包含其他区域值的所有单元格

w8f9ii69 于 2023-04-07 发布在其他

关注(0)|答案(1)|浏览(222)

我需要突出显示range（r）中包含另一个范围（字典）中的值的所有单元格。
此代码仅突出显示字典范围中每个单元格的第一次出现。

Sub SearchAndFormat_Click()   
    Dim Dictionary As Variant
    Dictionary = Range("L5:L9")
    
    Dim r As Range, cell As Variant
    Set r = Application.InputBox("Select range", "Selection Window", Type:=8)
    r.ClearFormats
    r.NumberFormat = "General"
    
    For Each subj In Dictionary
        For Each cell In r
            Set target_cell = r.Find(subj, LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlNext)
            If Not target_cell Is Nothing Then
                target_cell.Interior.ColorIndex = 4
            End If
        Next
    Next
End Sub

我曾经有一个没有嵌套循环的代码版本，但它只会突出显示字典范围中第一个值的第一次出现。

For Each cell In r
    Set target_cell = r.Find(Dictionary, LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlNext)
    If Not target_cell Is Nothing Then
        target_cell.Interior.ColorIndex = 4
    End If
Next

excel

来源：https://stackoverflow.com/questions/73674450/highlight-all-cells-in-range-which-contain-values-from-another-range

1条答案

按热度按时间

dsf9zpds1#

如果你使用Find来查找所有等于目标值的值，那么它应该是一个Do ... Loop，并积极使用参数After:=...。

Set x = MyRange.Find(target)
If Not x Is Nothing Then
   Start = x.Address
   Do
      Debug.Print x.Address, x.Value
      Set x = MyRange.FindNext(After:=x)
   Loop While x.Address <> Start
End If

但是，当你循环遍历字典和感兴趣的范围中的每个值时，没有必要使用Find：

Sub test_colorizing()
Dim d As Range
Dim r As Range
Dim x, y
    Set d = Range(...)   ' Dictionary
    Set r = Range(...)   ' Range of interest
    For Each x In r
        For Each y In d
            If x = y Then
                x.Interior.ColorIndex = 4
                Exit For   ' go to the next word in r
            End If
        Next
    Next
End Sub

使用Find时的外观：

Sub test_colorizing_with_find()
Dim dict As Range    ' Dictionary
Dim rng As Range     ' Range of interest
Dim cell, word, start
    Set dict = Range(...)
    Set rng = Range(...)
    For Each word In dict
        Set cell = rng.Find(word)
        If Not cell Is Nothing Then
            start = cell.Address
            Do
               cell.Interior.ColorIndex = 4
               Set cell = rng.FindNext(cell)
            Loop While cell.Address <> start
        End If
    Next
End Sub

赞(0）回复(0）举报 2023-04-07

我来回答

excel 突出显示区域中包含其他区域值的所有单元格

1条答案

相关问题

热门标签

最新问答