我需要以下腳本的幫助。我需要突出顯示范圍（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
任何幫助是極大的贊賞！
uj5u.com熱心網友回復：
如果您使用Findin order 查找等于目標的所有值，那么它應該是一種Do ... Loop主動使用引數的 aAfter:=...例如：
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
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/506276.html