Hyperlink to today's date

golferboy

New Member
Joined
Aug 6, 2013
Messages
4
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
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Fishboy

Well-known Member
Joined
Feb 13, 2015
Messages
4,261
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:

Rich (BB 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:

Rich (BB 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

New Member
Joined
Aug 6, 2013
Messages
4
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

Well-known Member
Joined
Feb 13, 2015
Messages
4,261
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,230
Messages
5,594,952
Members
413,954
Latest member
mrsandy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top