When dropdown changes to specific text add date to a cell

[Nick70]

Hi,

I have a range in sheet1 (range J2:J10) that has data validation, so each cell in this range has a dropdown which has 4 different text options: not started, started, ongoing, complete.

I would like to have a macro that if dropdown selection (in range J2:J10) is either 'started' or 'ongoing' then the corresponding cell in range N2:N10 is filled in with today's date.

For example if one selects 'started' or 'ongoing' in cell J4 then cell N4 will be filled in with today's date.
If one selects 'started' or 'ongoing' in cell J8 then cell N8 will be filled in with today's date and so on.

It is important that today's date that appears in range N2:N10 is a hardcoded date that does not change the following day (so it should not be formula (=today()).
Also if text not 'started' or 'complete' is selected in any cell in range J2:J10 then any text in corresponding cell in range N2:N10 must be deleted (so if there was a date this should be deleted).

What code can I use to do this?

Thanks,
Nix

 

[Fluff]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Not Intersect(Target, Range("J2:J10")) Is Nothing Then
      Select Case Target
         Case "started", "ongoing"
            Target.Offset(, 4).Value = Date
         Case Else
            Target.Offset(, 4).Value = ""
      End Select
   End If
End Sub

This needs to go in the sheet module for the relevant sheet

 

[Nick70]

Great that is perfect.

It is doing exactly what I wanted.

Thanks,
Nic

 

[Fluff]

You're welcome & thanks for the feedback.

 

[hrayani]

If Target.CountLarge > 1 Then Exit Sub

Hi fluff,
just for learning purpose

what is this line used for & what does this do

 

[Fluff]

It stops the code from running if more than one cell is changed.

 

[hrayani]

It stops the code from running if more than one cell is changed.

you mean more than one cell at a time .... right ???

 

[Fluff]

That's right.

 

[hrayani]

That's right.

Thanks

 

[Fluff]

My pleasure.