Auto Date Stamp for each cell in a given range (row and column)

[I thought i knew Excel]

Hey any help would be highly appreciated

as a control feature i would like to know when a set of data was last updated. My data is as followers for the columns A,B & C
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Company X[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Header[/TD]
[TD]Value[/TD]
[TD]Date[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Name1[/TD]
[TD]x[/TD]
[TD]31/06/2015[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Name2[/TD]
[TD]y[/TD]
[TD]31/06/2015[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Name3[/TD]
[TD]z[/TD]
[TD]31/06/2015[/TD]
[/TR]
[TR]
[TD]...[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]26[/TD]
[TD]until row 26[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

i would like excel to automatically give a date stamp, when a value is updated. I only want this to work for the values from B4 to B26, and the Date stamps to be input respectively in C4 to C26. I have other data below row 26 that should not be affected by this.

i am working on the following code, but i can not get it to work for the full range of rows. i can only make it work for one row.

    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim Cell As Range
        For Each Cell In Target
        With Cell
        If .Column = Range("B:B").Column And .Row = Range("4:4").Row Then
        Cells(.Row, "C").Value = Int(Now)
        End If
        End With
        Next Cell
    End Sub

I hoped I could change

.Column = Range("B:B").Column And [B].Row = Range("4:4").Row[/B] Then

to

.Column = Range("B:B").Column And [B].Row = Range("4:26").Row[/B] Then

but with no luck, how do i specify the larger range, without it working on all rows?

all the best
Marc

 

[JackDanIce]

Welcome to the board. Try:

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("B4:B26")) Is Nothing Then
    Target.Offset(0, 1).value = Int(Now)
End If
    
End Sub

 

[healey21]

Select your sheet tab and use right mouse button and select View Code change drop downs at top to Worksheet (first on Left) and Change (On the right) use this code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("b4:b26")) Is Nothing Then
With Target(1, 2)
.Value = Date 'or use Now() if you want date and time
.EntireColumn.AutoFit
End With
End If
End Sub

 

[I thought i knew Excel]

Thanks JackDanIce & healey21, both works like a charm and thank you for quick response time.

one follow up question, why dose "now" in JackDanIce solution not give the time, while "now()" in healey21 version does?

just because of the extra time feature, i think i will go with healey all though with out the ".EntireColumn.AutoFit"

 

[JackDanIce]

Try below if you want date and time:

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("B4:B26")) Is Nothing Then
    Target.Offset(0, 1).value = Now
End If
    
End Sub