Possibly a VBA command needed

[rupjon]

Hello All!

I am trying to build a excel sheet that can do the following.
When some one selects a yes or no (from the list) in Column B it than copies the Cell E10 and pastes special text/value only into the corresponding row Cell E.

Eg click yes on B4 pastespecial the static date and time E4, click no B8 pastes static date time E8. (i dont want the times changing when any other cell changes (except for the E10 NOW() fuction)

B C D E
4 Yes/No static Date and time
5 Yes/No
6 Yes/No
7 Yes/No
8 Yes/No
9 Yes/No
10 =Now() (updating date and time)

Please i have been stuck here now for a while.

Any help would be appreciated.

Thank you!

 

[Danerida]

This works. You may need to play with the formatting to get the date/time the way you want it to look:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Cells(1, 2).Resize(ActiveSheet.UsedRange.Rows.Count)) Is Nothing Then
    If Target.Value = "Yes" Then Target.Offset(0, 3).Value = Format(Now(), "hh:mm DD/MM/YYYY")
End If
End Sub

Dan

 

[rupjon]

This works. You may need to play with the formatting to get the date/time the way you want it to look:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Cells(1, 2).Resize(ActiveSheet.UsedRange.Rows.Count)) Is Nothing Then
    If Target.Value = "Yes" Then Target.Offset(0, 3).Value = Format(Now(), "hh:mm DD/MM/YYYY")
End If
End Sub

Dan

Thank you Dan! it works well! Thanks again!