Hyperlink to today's date

[golferboy]

Good day, my question is as follows,

I have =now() date in column A4. In column B4, I want a hyperlink to take me to today's date in Cell BS4. I want this hyperlink to move every date one cell to the right.

Thanks
Bernard

 

[Fishboy]

Good day, my question is as follows,

I have =now() date in column A4. In column B4, I want a hyperlink to take me to today's date in Cell BS4. I want this hyperlink to move every date one cell to the right.

Thanks
Bernard

Hi golferboy,

If you don't mind some simple VBA helping to make your life easier you could try the following:

- Change the hyperlink in cell B4 to point back at itself (so the link it points to is also B4)
- Right-click on the sheet name and select view code
- In the window that opens copy and paste in the following code:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Call FindDate
End Sub

- Whilst still in the VBA developer window, find your workbook name in the project panel on the left hand side then right-click on ThisWorkbook and select Insert-->Module
- In the new window that opens copy and paste the following code:

Sub FindDate()
' Defines variables
Dim sRange As Range, Rng As Range, FindString As Date
    ' Sets search range as the used range of the active sheet
    Set sRange = ActiveSheet.UsedRange
        ' Sets FindString as Today's date
        FindString = Date
            ' With the search range
            With sRange
                ' Set Rng as the cell where the value is found
                Set Rng = .Find(What:=FindString, _
                                After:=.Cells(1), _
                                LookIn:=xlValues, _
                                LookAt:=xlWhole, _
                                SearchOrder:=xlByRows, _
                                SearchDirection:=xlPrevious, _
                                MatchCase:=False)
                    ' If Rng exists then
                    If Not Rng Is Nothing Then
                        ' Select the cell
                        Rng.Select
                    End If
            End With
End Sub

- Save the document as a macro enabled workbook (.xlsm format)

With the above set up clicking on the hyperlink in cell B4 will always select whatever cell has today's date without you needing to constantly update the hyperklink or code.

 

[golferboy]

Thank you very much Fishboy, I will certainly try this. Thanks again

 

[Fishboy]

Thank you very much Fishboy, I will certainly try this. Thanks again

You're welcome, I hope it suits your requirements.

 

[golferboy]

Hi Fishboy, I tried your method but it doesn't want to work for me. If my mouse goes over B5, I can see the hyperlink but when I click on it, nothing happens. Doesn't want to move to today's date. Hyperlink in B5 is =Hyperlink(B5,"Test"). I have copied your code into the cell and no go!! Any other suggestions? Thanks

 

[Fishboy]

Hi Fishboy, I tried your method but it doesn't want to work for me. If my mouse goes over B5, I can see the hyperlink but when I click on it, nothing happens. Doesn't want to move to today's date. Hyperlink in B5 is =Hyperlink(B5,"Test"). I have copied your code into the cell and no go!! Any other suggestions? Thanks

Hmm, curious....

HERE is my test document with the code in working as described (I think I made it B4 and not B5 by mistake, but the concept is the same). See if that works for you and if so look at how the code is applied compared to how you set yours ut p to see if there were any differences.