Solved: Enter today's date based on another cell

[U_Shrestha]

Hi all,

if(and(a1<>"p",a1<>"c"),b1=today(),""))

Can the today() part not change everyday? If a1 has "p" or "c" then B1 should show today's date, but it should not change everyday but should indicate the date when the a1 cell is filled. Any ideas? Thanks.

[Bob Phillips]

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1" '<== 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 <> "p" And .Value <> "c" 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.

[RonMcK]

if(and(a1<>"p",a1<>"c"),b1=today(),""))

Can the today() part not change everyday? If a1 has "p" or "c" then B1 should show today's date, but it should not change everyday but should indicate the date when the a1 cell is filled. Any ideas? Thanks.
Hmmm? Your formula says if a1<>"P" and a1<>"c" then set b1 to today's date. However, your explanation says if a1="p" or a1="c" then set b1 to today's date.

A couple of questions:
Which way do you want the process to work?
What should happen to the date in b1 when you change a1 from one good value to:
another good value?
a not good value?Thanks!

[U_Shrestha]

Sorry, my formula does not indicate what I want.

What I want is, if A1 has EITHER "p" OR "c", then B1 should have today's date, however, this date should not be volatile. My table has a dynamic range.

I will use xld's code in couple of hours and post feedback. Thanks.

[U_Shrestha]

Actually xld's code worked great for me. Just had to tweak a bit. Thanks so much xld.

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A:a" '<== 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 = "p" Or .Value = "c" Then

.Offset(0, 1).Value = Date
.Offset(0, 1).NumberFormat = "mm/dd/yy"
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub