|
- Private Sub Worksheet_Change(ByVal Target As Range)
- If Target.Address = "$C$2" Then
- Dim i As Integer, j As Integer, m As Integer, d, arr, KWL, temp, CM, arro(1 To 1000)
- Set d = CreateObject("Scripting.Dictionary")
- Columns("A:A").Font.ColorIndex = xlAutomatic
- KWL = Range("C2")
- arr = Range("A1:A" & Range("A65536").End(xlUp).Row)
- For i = 1 To UBound(arr)
- For j = 1 To Len(arr(i, 1)) - KWL + 1
- temp = Mid(arr(i, 1), j, KWL)
- If d.Exists(temp) Then
- d(temp) = d(temp) & ",A" & i
- Else
- d.Add temp, "A" & i
- End If
- Next j
- Next i
- For Each CM In d.keys
- If UBound(Split(d(CM), ",")) > 0 Then
- m = m + 1
- arro(m) = CM & d(CM)
- Range(d(CM)).Font.Color = -16776961
- End If
- Next
- Range("B1:B1000") = Application.Transpose(arro)
- End If
- End Sub
复制代码 |
|