Moving Cursor to Current Date (Column) but same row that I was in before clicking hyperlink or vba button

[BrerRabbit]

I would like to try some VBA code that remembers where the cursor was (row number) but go to the required column, and I have no idea where to even start.

I currently have a hyperlink that allows me to go to the today's date column, but always goes to the top of the page, instead of the row the cursor was at before clicking the hyperlink to go to today's date:

=HYPERLINK("#"&SUBSTITUTE(ADDRESS(1,MATCH(B1,11:11,0),4),1,"")&16,"Find Today")

(Current date is in B1 in my project).

If my mouse was in M4 ie 8 Jan Blue and I click the Hyperlink, it will take me to B1 28 Dec. I need to go to B4 ie 28 Dec Blue.

Is there a way to do this? This is obviously a small example of a large project I am currently working on.

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

1

28/12/2024

28-Dec-24

29-Dec-24

30-Dec-24

31-Dec-24

1-Jan-25

2-Jan-25

3-Jan-25

4-Jan-25

5-Jan-25

6-Jan-25

7-Jan-25

8-Jan-25

9-Jan-25

10-Jan-25

11-Jan-25

12-Jan-25

2

Red

3

White

4

Blue

5

Pink

6

Green

7

Brown

8

Black

9

Yellow

Thank you in advance.

 

[kevin9999]

Rather than a hyperlink, would a before double click event be acceptable? If so, put the following code in the worksheet module of the sheet in question (right click the sheet tab name, select View Code and place in the code window that appears right of screen - save the file as either macro-enabled or binary). That way, if you're in M4 & double-click the mouse, the cursor will go to B4.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, cancel As Boolean)
    On Error GoTo Escape
    Application.EnableEvents = False
    cancel = True
    
    Application.Goto Cells(Target.Row, Range("1:1").Find(Date).Column)
    
Continue:
    Application.EnableEvents = True
    Exit Sub
Escape:
    MsgBox "Today's date not found in row 1"
    Resume Continue
End Sub