Help with VBA?

[mcintoshmc]

I'm using this VBA, and it worked perfectly the first day, but over the weekend I came back to work, and it only works in the cells towards the bottom of the sheet, but not those on the top. I have no idea why it's doing that.

Basically, column O is a notes column. Once I make a change in said column, it enters the date in column Z. I have conditional formatting which will color the entire row.

Can anyone tell me why it is only working in some rows, but not all? The range does say "O:O", so not sure whats happening.


Private Sub Worksheet_Change(ByVal Target As Range)
Dim rInt As Range
Dim rCell As Range
Dim tCell As Range

Set rInt = Intersect(Target, Range("O:O"))
If Not rInt Is Nothing Then
For Each rCell In rInt
Set tCell = rCell.Offset(0, 11)
If IsEmpty(tCell) Then
tCell = Now
tCell.NumberFormat = "mmm d, yyyy hh:mm"
End If
Next
End If
End Sub

 

[mcintoshmc]

Actually, I see the problem. It will only work if the cell is blank. If the cell has a previous date, it will not enter the new date. Is there a way for this to work when the cell is not blank?

 

[Fluff]

Your code checks is col Z is empty, if it isn't then nothing will happen.
Do you want col Z changed if a value is already there?

 

[Fluff]

Seeing post#2, try

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim rInt As Range
   Dim rCell As Range
   Dim tCell As Range
   
   Set rInt = Intersect(Target, Range("O:O"))
   If Not rInt Is Nothing Then
      For Each rCell In rInt
         With rCell.Offset(0, 11)
            .Value = Now
            .NumberFormat = "mmm d, yyyy hh:mm"
         End With
      Next
   End If
End Sub

 

[mcintoshmc]

worked perfectly. Thank you!

 

[Fluff]

You're welcome & thanks for the feedback