How to add Hyperlinks in Excel using VBA Macro

Last updated: 24th June 2024

A Hyperlink is a reference to add a link between two data in two different locations. We have seen and used links on websites. Similarly, we can add links to data within an Excel worksheet. Here, in this article I'll show you how to add hyperlinks in cells based on some conditions using VBA.

Let us assume, I have some data in "Sheet1" showing annual sales of different regions for the "first year" of business. There is another column showing data for the "second year". I will add figures for every region.

Here's the condition for adding a "link". When a user enters sales figures for the "second year", the macro checks if the "value" is less than the value in the "first year". If the amount is less, it will add a link next to the figure and add another link in Sheet2 linking to the figure in Sheet1 (with less amount).

add hyperlink in cell on condition

The VBA Code

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Row = 1 Then Exit Sub             ' IF ITS A HEADER, DO NOTHING.

    If Trim(Target.Text) <> "" Then
        addHyperLink
    End If

End Sub

Sub addHyperLink()

    On Error GoTo ErrHandler
    Application.ScreenUpdating = False
    
    Dim myDataRng As Range
    Dim cell As Range
    
    Set myDataRng = Range("D1:D" & Cells(Rows.Count, "D").End(xlUp).Row)
    
    Application.Worksheets("Sheet2").Columns(1).ClearContents
    Application.Worksheets("Sheet2").Cells(1, 1) = "Critical Log"
    
    For Each cell In myDataRng
    
        If cell(myDataRng.Row, 1).Text < Val(cell(myDataRng.Row, 0).Text) Then
        
            ' ADD ADDRESS.
            Application.ActiveSheet.Hyperlinks.Add _
                Anchor:=Application.ActiveSheet.Cells(cell.Row, cell.Column + 1), _
                    Address:="mailto:xyz@email.com?subject=Sales Report", _
                        SubAddress:="", _
                            ScreenTip:="Critical", _
                                TextToDisplay:="Mail this Figure"
            
           
            ' ADD SUB-ADDRESS
            Application.Worksheets("Sheet2").Hyperlinks.Add _
                Anchor:=Application.Worksheets("Sheet2").Cells(cell.Row, 1), _
                    Address:="", _
                        SubAddress:=Application.ActiveSheet.Name & "!" & cell.Address, _
                            ScreenTip:="Critical", _
                                TextToDisplay:="Check Figure"
        End If
    Next cell

    Set myDataRng = Nothing
ErrHandler:
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

The first method will add a Hyperlink (in Sheet1) if the "amount" is less than the previous year's amount.

The second method adds a Hyperlink in Sheet2 (first column). It does have an Address, but a SubAddress linking back to the cell with less amount in Sheet1. (See picture above)

Hyperlinks.Add() Method

I have used this method twice in the above example.

The Hyperlinks.Add() method will add a hyperlink to a specified range. The method takes five parameters.

Syntax

Add(Anchor As Object, Address As String, [SubAddress], [ScreenTip], [TextToDisplay]) as Object

The first two parameters are required and the rest are optional.

Anchor (required) – An excel Range to add the anchor for the Hyperlink
Address (required) – The address value of the Hyperlink
SubAddress (optional)– SubAddress property of the Hyperlink
ScreenTip (optional) – Looks similar to a Tooltip. Shows a little message when we hover the mouse over the Hyperlinks.
TextToDisplay (optional) – The Hyperlink label showing an underlined text for users to click the link

← PreviousNext →