I have a table called Table1
In Column B, I have the ticket number. e.g: 76537434
Requirement: when any change happens in any cell in column B, that cell (Target cell) to be changed into a hyperlink such that the hyperlink address would be example.com/id=76537434
Cell value i.e. 76537434 must remain the same
Add this event handler to your worksheet's code module:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 2 Then Exit Sub
Target.Hyperlinks.Delete ' or Target.ClearHyperlinks to conserve the formatting
Me.Hyperlinks.Add Target, "http://example.com/id=" & Target.value
End Sub
The following Worksheet_Change event should be able to solve your problem:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
Dim tmp As String
If Intersect(Range("B:B"), Target) Is Nothing Then Exit Sub
For Each cell In Target
If cell.Column = 2 Then
Application.EnableEvents = False
tmp = cell.Value2
cell.Parent.Hyperlinks.Add _
Anchor:=Cells(cell.Row, 2), _
Address:="http://example.com/id=" & tmp, _
TextToDisplay:=tmp
Application.EnableEvents = True
End If
Next cell
End Sub
Note, that you must copy it to the sheet and not into a separate module.
=HYPERLINK(E14&F14,"Name")
where cell E14 contains "http://www.example.com/id=" and cell F14 contains "76537434". This soultions doesn't need VBA macros.
Hyperlink()function call in a separate helper-column would be fine then you can also go with a solution similar to this one: stackoverflow.com/questions/42977982/…