用vba生成Excel中内容相关超链接

对excel表格汇总的某一列设置超链接的vba代码

Sub LinkSwitch()
Dim selected As Range
For Each c In Selection
    If c.Hyperlinks.Count > 0 Then
    ElseIf c.Value <> "" Then
        If ActiveSheet.index = 1 Then
            link = "http://192.168.1.1/bugzilla/show_bug.cgi?id=" & c.Value
        Else
            link = "http://192.168.1.2/bugzilla/show_bug.cgi?id=" & c.Value
        End If
            ActiveSheet.Hyperlinks.Add Anchor:=c, Address:=link
    End If
Next c
End Sub

一段修改中文bug状态的vba代码

Sub ChangeState()
Dim selected As Range
For Each c In Selection
    If (c.Value = "已关闭" Or c.Value = "已完成" Or c.Value = "回归测试") Then
        c.Value = "fixed"
    ElseIf c.Value = "未完成" Then
        c.Value = "open"
    End If
Next c
End Sub