Opening a hyperlink created by variables upon cell change

[matthewkeul]

Hi!

I have been searching these forums to find a solution for what I want, but alas I can't find a suitable solution.

What I want to do is that, when Testing is selected from a dropdown (dropdown created already), an internet explorer window opens.

I have written the following code (I didn't ever use VBA up until three days ago so cut me some slack):

Private Sub Worksheet_Change(ByVal Target As Range)
Dim xCellcolumn As Integer
Dim xTimeColumn As Integer
Dim xOthercolumn As Integer
Dim xRow, xCol As Integer
xCellcolumn = 3
xTimeColumn = 7
xOthercolumn = 9
xRow = Target.Row
xCol = Target.Column
If Target.Text <> "" Then
    If xCol = xCellcolumn Then
       Cells(xRow, xTimeColumn) = Now()
    End If
End If
If xCol = xCellcolumn Then
If Target.Text = "Tested" Then
Target.Interior.ColorIndex = 3
ElseIf Target.Text = "Testing" Then
Target.Interior.ColorIndex = 4
ElseIf Target.Text = "" Then
Target.Interior.ColorIndex = 2

If Target.Text = "Testing" Then
If xCol = xCellcolumn Then
ActiveSheet.FollowHyperlink Address:="http://www.hln.be", NewWindow:=True
End If
End If
End If
End If
End Sub

What I want to do is that, upon the cell changing to the status "Testing" that a hyperlink opens. This hyperlink is a construct where the value of the cell to the left of testing in a range between A2 and A1822 needs to be inserted at various points. My formula would therefore look like:
"mylink.be" & c.Value & "?/thispath" & c.Value & "endpath".

I think that I will need to find my range as c, and then create a for each so

dim c as range
for each c in range("a2:a1822")
ActiveSheet.Hyperlinks.Add Anchor:=c, Address:= FolderName & c.Value & path & c.Value, TextToDisplay:=c.Value

If Target.Text = "Testing" Then
If xCol = xCellcolumn Then
ActiveSheet.FollowHyperlink Address:="c", NewWindow:=True
End If
End If

Could anyone help me to do this? I must say that I find VBA in excel so insanely useful and nice to learn! I wish I would've done so sooner.

 

[matthewkeul]

I have adapted my code.
c2:c1822 now includes the link.

'MKE: SUB change defines that this must all appear upon a change in the worksheet
Private Sub Worksheet_Change(ByVal Target As Range)
'MKE: Define all of the variables we need in the functions
Dim xCellcolumn As Integer
Dim xTimeColumn As Integer
Dim xOthercolumn As Integer
Dim xRow, xCol As Integer
Dim c As Range
'MKE: Which columns are important for our changes - we just defined the variables as integers and the integers are the column numbers
xCellcolumn = 4
xTimeColumn = 8
xOthercolumn = 10

'MKE: Define the targets for the changes which is an intersection of a row and a column
xRow = Target.Row
xCol = Target.Column
'MKE: If the target text is different from blank the function will execute another if function
If Target.Text <> "" Then
    If xCol = xCellcolumn Then
    
'MKE: For every cell that was changed within the intersection of the timecolumn and the rows the date and timestamp are added
       Cells(xRow, xTimeColumn) = Now()
    End If
End If
'MKE: Create a function that changes the colour of the Cellcolumn cells
If xCol = xCellcolumn Then
If Target.Text = "Tested" Then
Target.Interior.ColorIndex = 3
ElseIf Target.Text = "Testing" Then
Target.Interior.ColorIndex = 4
ElseIf Target.Text = "" Then
Target.Interior.ColorIndex = 2
'MKE: Now we're going to try and add hyperlinks and automatically open them when the status of the activecell
For Each c In Range(c2, c1822)
Next
If Target.Text = "Testing" Then
ActiveCell.Hyperlinks.Add Anchor:="c", Address:="c"
ActiveCell.FollowHyperlink Address:="c", NewWindow:=True
End If
End If
End If
End Sub

No link is added or executed

 

[matthewkeul]

Small bump cos the problem is still existent.