How to create a function that fills the current date in an adjoining cell

[Dave K]

Hello, I am trying to create a macro (or perhaps there is a simple
function) that will automatically fill the current date in the left
column (Column A) when I enter any text or data in a cell in Column
B.

So for example, I want cell A4 to read 06/26/07 if I enter anything in
cell B4. And then in two weeks, if I add new info in cell B5, I want
cell A5 to read the date of that entry.

Anyone know of a good formula or macro that would do this?

Thanks in advance for any suggestions you can provide.

 

[JMay]

Paste the below code into the Sheet module of interest:
Also, Click Column A's Column Header -- And Format the entire
Column as a Date...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 Then
Target.Offset(, -1).Value = Date
End If
End Sub

 

[ssGuru]

Paste the below code into the Sheet module of interest:
Also, Click Column A's Column Header -- And Format the entire
Column as a Date...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 Then
Target.Offset(, -1).Value = Date
End If
End Sub








- Show quoted text -

How about referencing more than one field? How would you change the
target and make the offset a fixed or named column?

If Target.Column = 2, Target.Column=3, Target Column=4..... Then

Target.Offset(, -1).Value = Date 'reference named column??

Dennis

 

[p45cal]

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column >= 2 And Target.Column <= 4 Then
Application.EnableEvents = False
Target.Offset(, -Target.Column + 1).Value = Date
Application.EnableEvents = True
End If
End Sub

Anything changed in single cells in in Columns B,C, or D results in a date
in column A. DOn't try pasting ranges more than one column wide though.
You could test for this by changing the IF statement to:
If Target.Column >= 2 And Target.Column <= 4 And Target.Columns.Count = 1 Then

 

[Dave K]

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column >= 2 And Target.Column <= 4 Then
Application.EnableEvents = False
Target.Offset(, -Target.Column + 1).Value = Date
Application.EnableEvents = True
End If
End Sub

Anything changed in single cells in in Columns B,C, or D results in a date
in column A. DOn't try pasting ranges more than one column wide though.
You could test for this by changing the IF statement to:
If Target.Column >= 2 And Target.Column <= 4 And Target.Columns.Count = 1 Then

--
p45cal









- Show quoted text -

Thanks for all replies! Much appreciated.