Solved: Date stamp in column L if forumlua in column W = 1

Hello,

Could someone help me with a worksheet change event, such that when a cell in column W (where W2=F2/G2, etc.) changes to 1, a today's date is posted in column L?

The column with the cell I will acutally change is F. So, when I change F2 to "4" (and G2 is "4"), W automatically becomes "1", and when W is "1", L should change to today's date (indicating the date when all 4 reviews on a paper came in).

Thank you,
Natasha

[VBA]Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim x As Date
If Range("w4").Value = 1 Then
Range("L4") = Date
End If
End Sub[/VBA]

vlina and Austenr,

IMO I prefer the Now() command better, since it shows the date and time the change was made.

HTH

Thank you, both, for your help. This works for row 4. How could I extend it to the entire column?

Thanks again,
natasha

[VBA]Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim x As Date
If Range("W:W").Value = 1 Then
Range("L:L") = Now()
End If
End Sub[/VBA]

Thanks - I had tried that, but excel doesn't seem to like it. It highlighted the "If Range ("W:W")... " line for debugging.

Change the "If Range ("W:W")..." to IF Range("W":"W").... and see what happens

[vba]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim x As Date
If Not Intersect(Target, Me.Range("W:W")) Is Nothing Then

If Target.Value2 = 1 Then

Me.Cells(Target.Row, "L").Value = Now()
End If
End If
End Sub
[/vba]

Changing it to "W":"W" gave me the following error:

Compile error:
Expected: list separator or )

xld, it worked if I manually change the W column to be "1". However, the W column is acutally a formula (something like =F5/G5, for example). The only number I change is in the F column, and W will equal (or show) "1" if F=G.

Thanks again for your help,

Natasha

Hai try this

Code:

---


For i = 1 To 65000

a = Cells(i, 23).Value

    If a = 1 Then
   
        Cells(i, 12).Value = Date
   
    End If

Next i

---

Quote:

---

Originally Posted by mohanvijay

Hai try this

Code:

---


For i = 1 To 65000

a = Cells(i, 23).Value

    If a = 1 Then
   
        Cells(i, 12).Value = Date
   
    End If

Next i

---

---

or this:

[vba]LastRowColA = Range("A65536").End(xlUp).Row

For i = 1 To LastRowColA

a = Cells(i, 23).Value

If a = 1 Then

Cells(i, 12).Value = Date

End If

Next i[/vba]

This will not process all 65000 + rows

A shot:

Code:

---

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rCell As Range
   
    If Not Application.Intersect(Target, Range("F:F")) Is Nothing Then
       
        For Each rCell In Target
            If rCell.Column = 6 _
            And rCell.Value = rCell.Offset(, 1).Value _
            And Not rCell.Value = vbNullString Then
                rCell.Offset(, 6).Value = Format(Date, "mmm d yyyy")
            ElseIf rCell.Column = 6 Then
                rCell.Offset(, 6).ClearContents
            End If
        Next
    End If
End Sub

---

Hope that helps,

Mark

Thank you for your help! Unfortunately, these don't seem to work - nothing happens on the worksheet.

Bobs code or Marks should work. Where are you putting the code. It has to go in the worksheet that you have your data in in the OP.

In the VBA editor (Alt + F11) double click the sheet your data is in from the VBA Project pane.

Next in the bigger window on the right where you see (General) click the drop down and change it to Worksheet. This will automatically bring up the Worksheet_Selection Change event. That is where the code goes. Have you tried that?

Yup, that's where I put it.

It doesn't seem to like the "If a = ..." part. Do you know of any reason why that might be?

try this instead:

[vba] Dim x As Date
If Not Intersect(Target, Me.Range("W:W")) Is Nothing Then
If Target.Value2 = 1 Then
Me.Cells(Target.Row, "L").Value = Now()
End If
End If [/vba]

Thank you, everyone and austenr for your help! It's much appreciated and helpful for learning.

It looks like the "a =" didn't work because of the data type.

GTO's code did work in the end (I must have put it in SelectionChange rather than regular Worksheet Change when I first tried).

Thank you again and hope to be able to repay the favor one day!

Glad you got your problem solved. Please mark your thread solved. Thanks.