Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("BY3:CC6")) Is Nothing Then
If Target.Count = 1 Then
If Len(Trim(Target.Value)) = 0 Then
oldValue = Target.Value
ElseIf Len(Trim(oldValue)) = 0 And Cells(Target.Row, (Target.Column - 77) * 4 + 5).Value = "URGENT" Then
' Your macro is called
MESSAGE
End If
End If
End If
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("BY3:CC6")) Is Nothing Then
If Target.Count = 1 Then
oldValue = Cells(Target.Row, (Target.Column - 77) * 4 + 5).Value
End If
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
roff = 0
If Not Intersect(Target, Range("G3:G6,K3:K6,O3:O6,S3:S7,W3:W6")) Is Nothing Then
roff = -2
ElseIf Not Intersect(Target, Range("BY3:CC6")) Is Nothing Then
roff = (Target.Column - 77) * 4 + 5 - Target.Column
End If
If roff <> 0 Then
If Target.Count = 1 Then
If Len(Trim(Target.Offset(, roff).Value)) = 0 Then
oldValue = Target.Value
ElseIf Len(Trim(oldValue)) = 0 And UCase(Target.Offset(, roff).Value) = "URGENT" Then
' Your macro is called
MESSAGE
End If
End If
End If
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
roff = 0
If Not Intersect(Target, Range("G3:G6,K3:K6,O3:O6,S3:S7,W3:W6")) Is Nothing Then
roff = -2
ElseIf Not Intersect(Target, Range("BY3:CC6")) Is Nothing Then
roff = (Target.Column - 77) * 4 + 5 - Target.Column
End If
If roff <> 0 Then
If Target.Count = 1 Then
oldValue = Target.Offset(, roff).Value
End If
End If
End Sub
Make a copy of the sheet in question and then use this event-driven macro in the worksheet module of the sheet
Open in new window
This macro works on sheet1 with the backup on sheet1 (2)