Automatic Date generation as per status changed.

[Guest]

Can some one give me solution to the following Problem:

I have the following Columns in the excel Sheet:
1. Status it has : New, Open,Close Reopen in a list.
2.New Date.
3 Open Date
4. Close Date
5. Reopen Date

When i change the status to Open, the Open Date Column Should take system
Date Automatically.
When i change the status to Close, the Close Date Column Should take system
Date Automatically.
Same for other status & Date columns.

 

[Dave Peterson]

This kind of thing takes an event macro.

Rightclick on your worksheet tab and select view code. Paste this into the code
window that you see.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myOffset As Long

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("a:a")) Is Nothing Then Exit Sub

On Error GoTo errHandler:

With Target
Select Case LCase(.Value)
Case Is = "new": myOffset = 1
Case Is = "open": myOffset = 2
Case Is = "close": myOffset = 3
Case Is = "reopen": myOffset = 4
Case Else: myOffset = 0
End Select

If myOffset > 0 Then
Application.EnableEvents = False
.Offset(0, 1).Resize(1, 4).ClearContents
With .Offset(0, myOffset)
.Value = Date
.NumberFormat = "mm/dd/yyyy"
End With
End If
End With

errHandler:
Application.EnableEvents = True

End Sub

Now swap back to excel and try typing something in A2 (say).