Date code problem.

[Tartesos]

I'm using this code in my sheet to get the date in the "L" column if any data is entered in the "B" column cells.

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 Then
Cells(.Row, "L").Value = Int(Now)
End If
End With
Next Cell
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
End Sub

It works fine .... but when it reach the row B:7152 stops adding the date on the "L:7152" when I add any data to the "B" cells on that row and below that row. Can anyone tell me how to change this for happening? Any way I can fix this? , meaby any other code? .. I tried few things but it did not work.

Regards.

 

[jamieleeuk]

Could you not use the following formula in L1?

=if(B1="","",now())

then copy that all the way down to L:x (where x is last row of data)

 

[dave3009]

Not sure the code works for me no matter how many rows.

try this

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
If Intersect(Target, Range("B:B")) Is Nothing Then Exit Sub
If Target.Columns.Count > 1 Then Exit Sub
For Each Cell In Target
Cell.Offset(0, 10).Value = Date
Next Cell
End Sub

 

[Tartesos]

Thanks for your tip jamieleeuk but the B cells need to be free of formulas.
dave3009, I have a question about your code. Where it's indicate that the date has to be display on the L column? From the code I can't figure out.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
If Intersect(Target, Range("B:B")) Is Nothing Then Exit Sub
If Target.Columns.Count > 1 Then Exit Sub
For Each Cell In Target
Cell.Offset(0, 10).Value = Date
Next Cell
End Sub

Can you explain a bit ??

Regards,******** type=text/javascript> vbmenu_register("postmenu_2687394", true); *********>

 

[dave3009]

My code will only execute if you are entering data into column B and if the dataset is one column wide. This allows you to enter into many rows in column B but not enter data if you have entered into B&C simulateously for example.

As you will only be entering into column B then column L is 10 columns to the right, hence Offset(0, 10) will get the date entered into it.

 

[Tartesos]

Thanks, I will try this today... thanks for the explanation.

Regards,