automatically enter date in multiple columns

0

Hi, 

I am looking for a code to auto-update date when data is entered in a specific column. currently i am using a code which is sucessfull in filling up my desired column. but i would like to enter another column of date which is automatically updated when data is entered in another column. so basically i am looking for 2 columns that get autofilled with date when their respective columns data is entered.   

original code used - 

Private Sub worksheet_change(ByVal target As range)
If target.Column <> 4 Then Exit Sub
If target.Cells.Count > 1 Then Exit Sub
With target.Offset(0, 3)
.Value = Now
.NumberFormat = "DD/MM/YYYY"
End With

End Sub

thank you 

Post Edited
CODE Tags: You must add [CODE][/CODE] tags around your code! (click the CODE button to do this when creating a post)
Answer
Discuss

Discussion

I edited your post to put the CODE tags around the code - makes reading it much easier.
don (rep: 1715) Mar 27, '19 at 5:00 am
Add to Discussion

Answers

0
Selected Answer

Try this Assume colums 4 and 9 and offset is constant at 3

Private Sub worksheet_change(ByVal target As range)
If target.Column = 4 or target.Column = 9 Then 
If target.Cells.Count > 1 Then Exit Sub With target.Offset(0, 3) .Value = Now .NumberFormat = "DD/MM/YYYY" End With End If End Sub
Discuss

Discussion

works perfectly !!
is there any ways i can change the offset, i need an offset at 3 and 5. 
thanks 
oliver (rep: 2) Apr 1, '19 at 4:06 pm
Add to Discussion
0

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)

' 1 = Column A; 2 = Column B; Etc.
If Target.Column = 2 Then

    ' Input current time and date into a cell 3 columns to the right of the currently changed cell
    Target.Offset(0, 3).Value = Now

End If

End Sub

It's a bit simpler and more concise than yours and it should be a little easier to understand.

Put as many IF conditions in there as you want and change the Column number accordingly.

Also, if that column will only ever hold the date, then just format the entire column how you want and you don't need to include that NumberFormat part in the macro.

Discuss

Answer the Question

You must create an account to use the forum. Create an Account or Login