Date Stamp to certain data

[red_jellybean]

I found the date stamp macro here and it works for the spreadsheet that I have. Except for one thing.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'when entering data in Col BW
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 75 Then 'can be modified, see below
n = Target.Row
If Excel.Range("BW" & n).Value <> "" Then
Excel.Range("BX" & n).Value = Now
End If
End If
enditall:
Application.EnableEvents = True
End Sub

On my spreadsheet in column BW, I have given the users a list to select. There are 4 options:
1) Load
2) Extend
3) Terminate
4) In Progress

I want the date stamp to work in column BX if the users select either "Load", "Extend" or "Terminate", but if the user selects "In Progress", I do not want the date stamp. I want to leave it blank. How do I make this exception?

Please help!

[Leith Ross]

Hello Red_Jellybean,

First, I am going to clean up the code for you. The Target is an Excel Range and represents the Range (one or cells) on the Worksheet. Therefore you can use the Range Properties with Target directly. You don't need to use Target.Cells.Column to return the column of the selected cell. Just use Target.Coulmn. Second, using the Select Case statement makes your code more flexible, faster, and easier to read.

Revised Code:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'when entering data in Col BW
On Error GoTo enditall
Application.EnableEvents = False
If Target.Column = 75 Then 'can be modified, see below
Select Case Target.Value
Case Is = "Load", "Terminate", "Progress"
Target.Value = Now
Case Is = "Progress"
Target.Value = ""
Case Else
End Select
End If
enditall:
Application.EnableEvents = True
End Sub

Sincerely,
Leith Ross