VBA Insert Date in End Cell on Worksheet Change

[rickincanada]

I have a workbook which contains a list of vehicle Stock Numbers and then the next 10 columns are websites those vehicles need to be listed on. The 11th (last) column is a date column. What I'm wanting (if it is possible) is to have the date column populate with the current date when the first of the 10 columns is set to Yes (validation is in place to only allow "Yes" or blank). If there is a date there currently I don't want to effect it. Here is a simple example of my workbook:

Stock # | eBay | Kijiji | You Tube | Date

A user will come in and enter a Stock # and then once they select Yes under any of the following columns I want the date to auto-populate into the Date column.

Thanks for any help you can provide!

Rick

 

[Marcelo Branco]

Hi,

Assuming your data in columns A to L (Date); beginning in row 2 and ending in row 1000; and headers in row 1
(adjust accordingly)

Maybe this

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("B2:K1000")) Is Nothing Then
        Application.EnableEvents = False
        If UCase(Target.Value) = "YES" And Cells(Target.Row, "L") = "" Then
            Cells(Target.Row, "L") = Date
        End If
        Application.EnableEvents = True
    End If
End Sub

HTH

M.

 

[rickincanada]

Works perfect! Thanks so much for your help!