Deleting Cells after change

milesorru

Board Regular
Joined
Nov 3, 2011
Messages
133
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
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

milesorru

Board Regular
Joined
Nov 3, 2011
Messages
133
Hi

would anyone be able to have a look at the above? I have great faith in you all

Many Thanks in advance

Miles
 

Watch MrExcel Video

Forum statistics

Threads
1,126,993
Messages
5,622,049
Members
415,875
Latest member
Tarali

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
Top