when 'status' changed, timestamp cell in correspondingly titled column

[tg590]

Hi,

I have a sheet with a list of unique values in column A, say 1 through 100.Each has a 'status' in column B. This status is selected from a drop-down menuof 6 options, say, I, II, III, IV, V and VI. These options each have a column assignedto them (columns C through H), and the cell in the first row of each of thesecolumns is called “I Date”, “II Date”, “III Date”, “IV Date”, etc.

I want to be able to assign a status to an entry from thedrop-down list (for example, I set the status of ‘45’ as ‘III’) and have Excelautomatically timestamp the cell in the corresponding column when I do that (i.e.the ‘III Date’ column in the row for ‘45’ will be timestamped). When I subsequentlychange the status of ‘45’ to ‘IV’, I want the cell in the ‘IV Date’ column tobe timestamped (and the ‘III Date’ to stay the same). This way I can track whenthe entries in column A move through each status.

I understand from the info I’ve found on the web thatthis is possible in VBA, but unfortunately I don’t know how to use it.

Any help would be gratefully appreciated.

 

[onlyadrafter]

Hello,

If I have interpreted your requirements, the below code should work.

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    If Intersect(Target, Range("b1:b100")) Is Nothing Then Exit Sub
        Select Case Target.Value
            Case "I"
                Cells(Target.Row, 3).Value = Now()
            Case "II"
                Cells(Target.Row, 4).Value = Now()
            Case "III"
                Cells(Target.Row, 5).Value = Now()
            Case "IV"
                Cells(Target.Row, 6).Value = Now()
            Case "V"
                Cells(Target.Row, 7).Value = Now()
            Case "VI"
                Cells(Target.Row, 8).Value = Now()
        End Select
    Application.EnableEvents = True
End Sub

This code needs to go into the relevant sheet code window, not a standard module.

Is it any use, or are there any issues?