Set cell to record date when adjacent cell is filled AND NOT RESET

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to have the date of input marked for a cell in another location
using an "IF" Function. The only problem is that when I open the sheet the
next day, it updates the date and does not solve my problem.

=IF(D3="R", TODAY(), "NA")

Any other options besides TODAY() that will help? I need it to only change
when data in the referenced cell is changed, not every time I open excel.
 
Use event code

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "D3" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If .Value = "R" Then
.Offset(0, 1).Value = Date
.Offset(0, 1).NumberFormat = "dd mmm yyyy"
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Bob,

Tried your code but still can't seem to get the field to fill in correctly.
I actually had a completely different cell's info change to the date but it
still updated automatically with the current day after I changed the date in
my calendar.
 
Bob,

Took some messing around but I got it to work and it's perfect! Thanks for
your support!
 
Back
Top