|This code should do what you asked for.|
I suggest that you try this in a backup copy of your workbook in case something goes terribly wrong.
1 - Press Alt-F11 to open the VBA editor.
2 - Find your workbook name in the left hand pane (VBAProject)
3 - Double click the ThisWorkbook module
4 - Paste the code below into right hand pane
5 - If Sheets(1) is not the correct sheet, modify the code as required.
6 - Save the workbook
7 - Close it
8 - Open it
9 - Smile if it works
Private Sub Workbook_Open()
'Determine date of Sunday in current week
curSunday = (Date - Choose(Weekday(Date), 0, 1, 2, 3, 4, 5, 6))
'Find that date in Sheet 1 Column A
Set s = .Find(curSunday)
'Select that cell
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.