|
Sub test()
Dim A, B, i, j, k, bol
Sheets(1).Select
Cells.Interior.ColorIndex = xlNone
Range("b2:b65536") = ""
A = Range("a1").CurrentRegion
B = Sheets(2).UsedRange
For i = 2 To UBound(A)
bol = False
For j = 2 To UBound(B)
For k = 2 To UBound(B, 2)
If B(j, k) <> "" Then
If InStr(A(i, 1), B(j, k)) Then
bol = True
A(i, 2) = A(i, 2) & "、" & B(j, UBound(B, 2))
Exit For
End If
End If
Next k
Next j
If bol Then A(i, 2) = Mid(A(i, 2), 2) Else Cells(i, 2).Interior.ColorIndex = 3
Next i
Range("a1").Resize(UBound(A), UBound(A, 2)) = A
End Sub
|
|