VBA Modifed Date

[Mr Jukebox]

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?

 

[Fluff]

How about

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]

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]

Using your code, try changing more than one cell at once & see what happens.

 

[Scott Huish]

You could also write it this way, which would allow you paste values into multiple cells.

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

 

[Mr Jukebox]

Thank you for the explanation and thanks for an alternative option.

 

[Fluff]

You're welcome & thanks for the feedback