Golf Spreadsheet - Autoclearing column after input and inserting "most minus"

[Iswearimnotdumb]

I'm working on a spreadsheet for my dad for his golfing group.. Thanks to this forum, we already have a code for automatically shifting the data over a column when new data is entered and we have the formulas in the spreadsheet configured so they do not shift with the data shifting.

What he's wanting is when data is entered into column B, it automatically clears column B after it moves into column E.

Also (not sure if this one is possible), that column D, which is the average of columns E through N, does not decrease more than 2 (value in A1).

Any help or tips would be greatly appreciated!

WCC Dogfight_ses_working.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	2
2	Players	Points	Net +/-	Quota	1	2	3	4	5	6	7	8	9	10	Total
3	Allen, Frankie	5		17	5		5	18	19	19	22	23	21	19	151
4	Allman, Willie			28	27	30	19	31	27	27	30	32	28	28	279
5	Arbaugh, Donnie			23	27	19	23	24	17	22	25	23	24	22	226
Sheet1

Cell Formulas
Range	Formula
D3:D5	D3	=AVERAGE(INDEX($E:$E,ROW()):INDEX($N:$N,ROW()))
O3:O5	O3	=SUM(INDEX($E:$E,ROW()):INDEX($N:$N,ROW()))

The code currently in the spreadsheet:

Private Sub Worksheet_Change(ByVal Target As Range) 'Excel VBA with more cells in the range.
'
If Not Intersect(Target, Range("B2:B55")) Is Nothing Then
Application.EnableEvents = False
'
Range("E" & Target.Row & ":M" & Target.Row).Cut Range("E" & Target.Row & ":M" & Target.Row).Cells(1).Offset(0, 1)
Range("E" & Target.Row) = Range("B" & Target.Row)
'
Application.EnableEvents = True
End If
End Sub

 

[johnnyL]

To clear the Column B value after it is moved, place the following before the 'Application.EnableEvents = True' line:
Range("B" & Target.Row) = ""