VBA SelectionChange on specific cells, copy into another sheet

[SamAnnElizabeth]

I'm new to writing VBA but I think I'm close to something. I want a macro that will run if a cell is changed in the range F10:F31. I want it to put in a different sheet the text of the cell address " Updated On " and the date. I think I'm close to something, but this isn't running.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target = "$F$10:$F:$31" Then
Sheets("List1").Select
Range(D1).End(xlDown).Offset(1, 0).Select
ActiveCell = Cell & " Updated On " & Date
End If
End Sub

So what I would want is that if F26 was updated, D2 would read "F26 Updated On 4/10/13". (I have a header in D1.) If F14 was changed next, D3 would read "F14 Updated On 4/10/13". I have the sub saved within the sheet where the F10:F31 is located. Any help?

 

[mumps]

Place this code in the worksheet code module.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("F10:F31")) Is Nothing Then Exit Sub
    Sheets("List1").Cells(Rows.Count, "D").End(xlUp).Offset(1, 0).Value = Target.Address(0,0) & " was updated on " & Date
End Sub

 

[SamAnnElizabeth]

Worked after I added an 'End If' command. Thanks!

 

[mumps]

I'm glad it worked out although I'm not sure why you needed an 'End If' line. The 'If ... Then' statement is on one line and therefore it shouldn't need an 'End If".

 

[SamAnnElizabeth]

Ah, my own fault. I put "End Sub" on the next line.

Maybe you can help me with something else. Is there a way to return the text of a box four columns left of the active cell instead of the cell address of the active cell? I tried this but it didn't work:


Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("F10:F31")) Is Nothing Then Exit Sub
Sheets("List1").Cells(Rows.Count, "D").End(xlUp).Offset(1, 0).Value = Target.Text(0,-4) & " was updated on " & Date
End Sub

 

[mumps]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("F10:F31")) Is Nothing Then Exit Sub
    Sheets("List1").Cells(Rows.Count, "D").End(xlUp).Offset(1, 0).Value = Target.Offset(0,-4).Value & " was updated on " & Date
End Sub

 

[HalfAce]

Sure. Assuming by "4 columns to the left", you mean column B, just change:
Target.Text(0,-4)
to
Target.Offset(0,-4)

Hope it helps.