VBA Modifed Date

Mr Jukebox

New Member
Joined
Sep 25, 2008
Messages
34
What i would like to do is if any cell in C5:H10 is modified i want the date and time to be placed in column B. Currently this is what i have

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("C5:H105")) Is Nothing Then Exit Sub ' C1:C100 is the price range
Target.Offset(0, -1).Value = Now() ' This will put the current date 6 columns over

End Sub

This works but will put the date and time of the change 1 cell to the left is there a way to get this information directed to only column B?
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
36,081
Office Version
365
Platform
Windows
How about
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
If Intersect(Target, Range("C5:H105")) Is Nothing Then Exit Sub ' C1:C100 is the price range
Range("B" & Target.Row).Value = Now()
End Sub
 

Mr Jukebox

New Member
Joined
Sep 25, 2008
Messages
34
That works perfectly can you explain why you used this line "If Target.CountLarge > 1 Then Exit Sub"

I changed the original code to this and it seems to work also just curious what the difference is.

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("C5:H105")) Is Nothing Then Exit Sub
Range("B" & Target.Row).Value = Now() ' This will put the current date in column B

End Sub
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
36,081
Office Version
365
Platform
Windows
Using your code, try changing more than one cell at once & see what happens.
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,941
Office Version
365, 2010
Platform
Windows
You could also write it this way, which would allow you paste values into multiple cells.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range, d As Range
Set d = Intersect(Target, Range("C5:H105"))
If d Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each c In d
    Range("B" & c.Row).Value = Now()
Next
Application.EnableEvents = True
End Sub
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
36,081
Office Version
365
Platform
Windows
You're welcome & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,090,409
Messages
5,414,236
Members
403,521
Latest member
Mattlake

This Week's Hot Topics

Top