|
楼主 |
发表于 2015-8-10 19:02
|
显示全部楼层
完善了一下VBA法,再次表示感谢!
- Private Sub Worksheet_Change(ByVal Target As Range)
- If Target.Count > 1 Then Exit Sub
- If Not Application.Intersect(Target, Union(Range("B3:Y3"), Range("B37:Y37"), Range("B71:Y71"))) Is Nothing Then
- Dim i%, j%, arr, d As Object
- Set d = CreateObject("scripting.dictionary")
- arr = Range("a2:a38")
- For i = 1 To UBound(arr)
- Set d(arr(i, 1)) = Cells(i + 1, 1)
- Next
- Application.EnableEvents = False
- For i = 2 To 38
- Cells(i, 1).Hyperlinks.Delete
- Next
- For i = 3 To 71 Step 34
- For j = 2 To 22 Step 4
- If d.exists(Cells(i, j).Value) And Cells(i, j) <> "" Then
- ActiveSheet.Hyperlinks.Add Anchor:=d(Cells(i, j).Value), Address:="", SubAddress:=ActiveSheet.Name & "!" & Cells(i, j).Address(0, 0), TextToDisplay:=Cells(i, j).Value
- End If
- Next
- Next
- Range("a1:a38").Font.Size = 9
- Application.EnableEvents = True
- Set d = Nothing
- End If
- End Sub
复制代码 |
|