Show the date a cell was last modified based on a condition

[Sourkraut]

Hello Everyone,

I did a couple searches on this forum but was unable to find something to fit my needs.

In Excel 2003, I have a cell in column 3 that is updated with either a "P, F, CB, A or n/a" using a List Validation. I know how to show a date last modified, but my boss wants to see the date last modified based on what criteria it was changed to.

So I have four columns to the right of column 3 labeled as "P, F, CB, A" (n/a is not needed). Under each of these columns, I'd like to show the date modified when column 3 is changed to that condition.

So once column 3 is changed to "P", I want column 4 (P) to be changed to that date modified. Once column 3 is changed to "F", I want column 4 to not change and I want column 5 (F) to change to the current date modified... I hope that makes sense

Any help would be awesome! Thank you!

 

[VoG]

Hello and welcome to MrExcel.

Try this: right click the sheet tab, select View Code and paste in

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Integer
If Target.Column = 3 Then
    Select Case Target.Value
        Case "F": i = 1
        Case "P": i = 2
        Case "CB": i = 3
        Case "A": i = 4
    End Select
    If i > 0 Then Target.Offset(, i) = Now
End If
End Sub

 

[Sourkraut]

Hello and welcome to MrExcel.

Try this: right click the sheet tab, select View Code and paste in

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Integer
If Target.Column = 3 Then
    Select Case Target.Value
        Case "F": i = 1
        Case "P": i = 2
        Case "CB": i = 3
        Case "A": i = 4
    End Select
    If i > 0 Then Target.Offset(, i) = Now
End If
End Sub

You're too good at excel! Thanks dude, this is perfect...

If we decide later that we want to not show the actual "time" changed, can you tell me how to take that off? to just show the date?

 

[VoG]

Just change Now to Date.

 

[Sourkraut]

Just change Now to Date.

Thanks again!