|
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
' 如果选中的单元格数量不是1,退出子过程
If Target.CountLarge <> 1 Then Exit Sub
' 如果选中的列不是第一列,退出子过程
If Target.Column <> 1 Then Exit Sub
' 如果选中的行是第一行,退出子过程
If Target.Row = 1 Then Exit Sub
' 如果选中单元格或相邻单元格内容为空,退出子过程
If Len(Target) * Len(Target.Offset(0, 1)) = 0 Then Exit Sub
' 获取排序后的字符串
Dim sortedString As String
sortedString = GetSortedString(Target.Offset(0, 1).Value)
' 高亮匹配的单元格
HighlightMatches sortedString
End Sub
Private Function GetSortedString(inputString As String) As String
Dim result As String
If InStr(inputString, ",") = 0 Then
result = inputString
Else
Dim arr As Variant
arr = Split(inputString, ",")
For j = 0 To UBound(arr)
For i = j + 1 To UBound(arr)
If Len(arr(i)) > Len(arr(j)) Then
tm = arr(i)
arr(i) = arr(j)
arr(j) = tm
End If
Next i
Next j
result = Join(arr, "|")
End If
GetSortedString = result
End Function
Private Sub HighlightMatches(pattern As String)
Application.ScreenUpdating = False
Dim lastRow As Long
lastRow = Cells(Rows.Count, 2).End(xlUp).Row
Dim dataRange As Range
Set dataRange = Range("B1:B" & lastRow)
dataRange.Font.ColorIndex = 0
With CreateObject("vbscript.regexp")
.Pattern = pattern
.Global = True
For j = 2 To lastRow
For Each match In .Execute(dataRange.Cells(j, 1).Value)
dataRange.Cells(j, 1).Characters(match.FirstIndex + 1, match.Length).Font.ColorIndex = 3
Next
Next j
End With
Application.ScreenUpdating = True
End Sub
|
|