Hi
I have (with some help from this forum) created a code for my tracker.
please see below.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count = 1 Then
If Target.Column = 4 And Target.Value <> "" Then
Application.EnableEvents = False
Select Case Target.Offset(, -3).Value
Case "Uni_UK"
Target.Offset(, 1).Value = Target.Value - 14
Target.Offset(, -1).Value = Target.Value - 28
Case "Turkey"
Target.Offset(, 1).Value = Target.Value - 7
Target.Offset(, -1).Value = Target.Value - 21
Case "SAF"
Target.Offset(, -1).Value = Target.Value - 10
Case Else
Target.Offset(, 1).Value = Target.Value - 1
Target.Offset(, -1).Value = Target.Value - 2
End Select
Application.EnableEvents = True
End If
If Target.Column = 1 And Target.Value = "SAF" Then
Application.EnableEvents = False
Target.Offset(, 4).Value = "N/A"
Application.EnableEvents = True
End If
If Target.Column = 1 And Target.Value = "SAF" Then
Application.EnableEvents = False
Target.Offset(, 10).Value = "N/A"
Application.EnableEvents = True
End If
If Target.Column = 1 And Target.Value = "SAF" Then
Application.EnableEvents = False
Target.Offset(, 8).Value = "N/A"
Application.EnableEvents = True
End If
If Target.Column = 1 And Target.Value = "SAF" Then
Application.EnableEvents = False
Target.Offset(, 11).Value = "N/A"
Application.EnableEvents = True
End If
If Target.Column = 1 And Target.Value = "SAF" Then
Application.EnableEvents = False
Target.Offset(, 12).Value = "N/A"
Application.EnableEvents = True
End If
If Target.Column = 1 And Target.Value = "SAF" Then
Application.EnableEvents = False
Target.Offset(, 13).Value = "N/A"
Application.EnableEvents = True
End If
If Target.Column = 1 And Target.Value = "SAF" Then
Application.EnableEvents = False
Target.Offset(, 16).Value = "N/A"
Application.EnableEvents = True
End If
If Target.Column = 15 And Target.Value = "Ready for TX" Then
Application.EnableEvents = False
Target.Offset(, 3).Value = Date
Application.EnableEvents = True
End If
If Target.Column = 15 And Target.Value >= 1 Then
Application.EnableEvents = False
Target.Offset(, 1).Value = Date
Application.EnableEvents = True
End If
If Target.Column = 15 And Target.Value = "Sent to ITFC" Then
Application.EnableEvents = False
Target.Offset(, 2).Value = Date
Application.EnableEvents = True
End If
End If
End Sub
My query is: When I select a status in column A, it changes data in various other columns.
Is there a way that if I select a different status in column A, the data that was selected originally will remove itself as part of the code?
I hope that I have explained myself well enough
Many Thanks
Miles
I have (with some help from this forum) created a code for my tracker.
please see below.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count = 1 Then
If Target.Column = 4 And Target.Value <> "" Then
Application.EnableEvents = False
Select Case Target.Offset(, -3).Value
Case "Uni_UK"
Target.Offset(, 1).Value = Target.Value - 14
Target.Offset(, -1).Value = Target.Value - 28
Case "Turkey"
Target.Offset(, 1).Value = Target.Value - 7
Target.Offset(, -1).Value = Target.Value - 21
Case "SAF"
Target.Offset(, -1).Value = Target.Value - 10
Case Else
Target.Offset(, 1).Value = Target.Value - 1
Target.Offset(, -1).Value = Target.Value - 2
End Select
Application.EnableEvents = True
End If
If Target.Column = 1 And Target.Value = "SAF" Then
Application.EnableEvents = False
Target.Offset(, 4).Value = "N/A"
Application.EnableEvents = True
End If
If Target.Column = 1 And Target.Value = "SAF" Then
Application.EnableEvents = False
Target.Offset(, 10).Value = "N/A"
Application.EnableEvents = True
End If
If Target.Column = 1 And Target.Value = "SAF" Then
Application.EnableEvents = False
Target.Offset(, 8).Value = "N/A"
Application.EnableEvents = True
End If
If Target.Column = 1 And Target.Value = "SAF" Then
Application.EnableEvents = False
Target.Offset(, 11).Value = "N/A"
Application.EnableEvents = True
End If
If Target.Column = 1 And Target.Value = "SAF" Then
Application.EnableEvents = False
Target.Offset(, 12).Value = "N/A"
Application.EnableEvents = True
End If
If Target.Column = 1 And Target.Value = "SAF" Then
Application.EnableEvents = False
Target.Offset(, 13).Value = "N/A"
Application.EnableEvents = True
End If
If Target.Column = 1 And Target.Value = "SAF" Then
Application.EnableEvents = False
Target.Offset(, 16).Value = "N/A"
Application.EnableEvents = True
End If
If Target.Column = 15 And Target.Value = "Ready for TX" Then
Application.EnableEvents = False
Target.Offset(, 3).Value = Date
Application.EnableEvents = True
End If
If Target.Column = 15 And Target.Value >= 1 Then
Application.EnableEvents = False
Target.Offset(, 1).Value = Date
Application.EnableEvents = True
End If
If Target.Column = 15 And Target.Value = "Sent to ITFC" Then
Application.EnableEvents = False
Target.Offset(, 2).Value = Date
Application.EnableEvents = True
End If
End If
End Sub
My query is: When I select a status in column A, it changes data in various other columns.
Is there a way that if I select a different status in column A, the data that was selected originally will remove itself as part of the code?
I hope that I have explained myself well enough
Many Thanks
Miles