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

tg590

New Member
Joined
Dec 9, 2018
Messages
1
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.
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Hello,

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

Code:
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?
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,941
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top