Selected Answer
I have re-worked your code, made some changes and added some comments but the logic of your sheet doesn't support your desire. Rules must be invented before they can be written into code.
Right now, the user selects an action and a date gets written into the appropriate column. Then the user selects another action and another date gets written to another column. The effect is that columns AW:BJ document the progress of a process.
Now, if the user wouldn't select another step in the process but simply deletes whatever is written in BK:BM what should happen? Obviously, there shoud be different rules depending upon which of the 3 columns was cleared. Equally obvious is that there should be a safety mechanism to prevent data deletion due to a typing error.
It seems to me that there should be a logical flow, like in column BL: "image requested", "image processed", "image uploaded". You would probably want a mechanism that only permits deletion of the last done step. To enforce that you would first need a mechanism to prevent skipping steps. So, control goes to the date columns. Like, If an image wasn't requested, it can't be processed. But if someone deletes "processed", then:
- Message, "Are you sure?"
- Delete the date for "processed"
- Reinstate the "requested" status (which must exist, with a date)
Then, what to do if the "requested" status is deleted? The whole thing is a lot more complex than the simple date stamping you have for now. On the other hand, the format of your existing code already pushes the limits of its potential performance. If you want to expand your demands on the code you must give it a shape that can handle expansion. Today I've done that for you but now it's yours to maintain.
Private Sub Worksheet_Change(ByVal Target As Range)
' 005
Dim C As Long
' Try not to interrupt the flow of the code
' either by GoTo or Exit commands
With Target
If .CountLarge = 1 Then
If Not Intersect(Target, Range("BK5:BM500")) Is Nothing Then
On Error Resume Next
C = Val(Application.VLookup(.Value, Sheets("Key").Range("B2:C20"), 2, False))
If C Then Cells(.Row, "AV").Offset(0, C).Value = Date
Err.Clear
End If
End If
End With
End Sub