Macro for updating the date, in a cell... maybe

[RogueRoustabout]

In simple terms here is the macro I want to write:
- When "Sheet1" is activated,
- If the date in "Range1" is current, end macro
- If the date in "Range1" is not current,
- Prompt the user to update (Msg box "do you want to update the date? yes/no")
- If user clicks "no" - end macro
- If user clicks "Yes" - update "Range1" with current date.

Below is the code that I tried to put together, but I am missing something.

Private Sub Worksheet_Activate()
   Range("N15:R15").Select
If Range("N15:R15").Value = Today Then
    Exit Sub
End If
response = MsgBox("Would you like to set the date to today?", vbYesNo)

If response = vbNo Then
    MsgBox ("Macro Ending")
    Exit Sub
End If
    Range("N15:R15").Select
    ActiveCell.FormulaR1C1 = Now
End Sub

Thank you for any help!

 

[MARK858]

Are Range("N15:R15") merged cells?

 

[RogueRoustabout]

Are Range("N15:R15") merged cells?

Yes they are. I was wondering, since they ARE merged, if just using "N15" as my range would work.

 

[MARK858]

Try the code below

Private Sub Worksheet_Activate()
    Dim response As Integer
    
    If Range("N15").Value = Date Then
        Exit Sub
    Else
        response = MsgBox("Would you like to set the date to today?", vbYesNo)

        If response = vbNo Then
            MsgBox "Macro Ending"
            Exit Sub
        End If
        
        Range("N15") = Date
    
    End If

End Sub

 

[RogueRoustabout]

Try the code below

Private Sub Worksheet_Activate()
    Dim response As Integer
   
    If Range("N15").Value = Date Then
        Exit Sub
    Else
        response = MsgBox("Would you like to set the date to today?", vbYesNo)

        If response = vbNo Then
            MsgBox "Macro Ending"
            Exit Sub
        End If
       
        Range("N15") = Date
   
    End If

End Sub

That's it! I am rusty in VBA and was unfamiliar with Date and Integers. Thank you for the quick help!

 

[MARK858]

Happy it helped and welcome to the forum