I have a hyperlink in cell a8. When someone uses this hyperlink I want cell d8 to show the date it was last accessed. In cell e8 I want to show a date two years on from the date in cell d8.

This would require VBA. Is that OK?

Constructing a macro like this is pretty straightforward. But you do have to know if this if for A8 only, or for a bunch of cells in column A starting at A8 and going down some distance.

For instance:

Cell Date Future Date
A8 E8 D8
A9 E9 D9
A10 E10 D10
etc....


Here's a macro to work A8 only, pretty simple:

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub

If Target.Address = "$A$8" Then
[E8] = Date
[D8] = DateValue(Year(Date) + 2 & "-" & Month(Date) & "-" & Day(Date))
End If


End Sub



Here's a macro for anywhere in column A where you click on a cell and it has a hyperlink:

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim HLink As Hyperlink
If Target.Cells.Count > 1 Then Exit Sub

If Target.Column = 1 Then
On Error Resume Next
Set HLink = Target.Hyperlinks(1)
If Not HLink Is Nothing Then
Application.EnableEvents = False
Range("E" & Target.Row) = Date
Range("D" & Target.Row) = DateValue(Year(Date) + 2 & "-" & Month(Date) & "-" & Day(Date))
Application.EnableEvents = True
End If
End If


End Sub




Pick on of these and put it into the Sheet module where you want this to occur.

1) Right-click on the sheet tab
2) Select View Code
3) Paste in the code given above
4) Close the VBeditor
5) Save your workbook as a macro-enabled workbook

Outstanding, thank you very much for your help. I am very very green with Excel and your answer has saved me no end of time. Thank you, thank you. It worked just as I needed it to.