I am getting help, yet I need further help. The formula I post below, works
for B3 and to the right. I need it to work exactly the same from B3 thru B100
and have numbers in the entire row work as B3 does . That is B49 moves to
C49, C49 moves to D49 etc.

Do I need a code for each line? That would seem horribly wrong.
Thank you: Check this. Works great for one line B3. It's a great code

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address < "$B$3" Or _
IsEmpty(Target) Or _
Not IsNumeric(Target) Then
Exit Sub
End If
If MsgBox("Use the new value " & Target & _
" as new Daily Entry?", vbYesNo + vbDefaultButton1 _
+ vbInformation, "Verify Entry") < vbYes Then
Target.ClearContents
Exit Sub
End If
'overwrite E3 with D3
Range("E3") = Range("D3")
'overwrite D3 with C3
Range("D3") = Range("C3")
'overwrite C3 with B3
Range("C3") = Target
'clear B3 for tomorrow
Target.ClearContents
End Sub

Need B1:B100 to work this way

Mark,

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B1:B100")) Is Nothing Or _
IsEmpty(Target) Or _
Not IsNumeric(Target) Or _
Target.Cells.Count 1 Then
Exit Sub
End If
If MsgBox("Use the new value " & Target & _
" as new Daily Entry?", vbYesNo + vbDefaultButton1 _
+ vbInformation, "Verify Entry") < vbYes Then
Target.ClearContents
Exit Sub
End If
'overwrite old values
Application.EnableEvents = False
Target.Resize(1, 3).Cut Target.Offset(0, 1)
Application.EnableEvents = True

End Sub


HTH,
Bernie
MS Excel MVP


"Mark" wrote in message
...
I am getting help, yet I need further help. The formula I post below, works
for B3 and to the right. I need it to work exactly the same from B3 thru B100
and have numbers in the entire row work as B3 does . That is B49 moves to
C49, C49 moves to D49 etc.

Do I need a code for each line? That would seem horribly wrong.
Thank you: Check this. Works great for one line B3. It's a great code

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address < "$B$3" Or _
IsEmpty(Target) Or _
Not IsNumeric(Target) Then
Exit Sub
End If
If MsgBox("Use the new value " & Target & _
" as new Daily Entry?", vbYesNo + vbDefaultButton1 _
+ vbInformation, "Verify Entry") < vbYes Then
Target.ClearContents
Exit Sub
End If
'overwrite E3 with D3
Range("E3") = Range("D3")
'overwrite D3 with C3
Range("D3") = Range("C3")
'overwrite C3 with B3
Range("C3") = Target
'clear B3 for tomorrow
Target.ClearContents
End Sub

Need B1:B100 to work this way

try:

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B1:B100"


On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
If IsEmpty(Target) Or _
Not IsNumeric(Target) Then
Exit Sub
End If
If MsgBox("Use the new value " & Target & _
" as new Daily Entry?", vbYesNo + vbDefaultButton1 _
+ vbInformation, "Verify Entry") < vbYes Then
Target.ClearContents
Exit Sub
End If
Row = Target.Row
Range("c" & Row).Resize(1, 2).Copy Range("d" & Row)
'overwrite C3 with B3
Range("C" & Row) = Target
'clear B3 for tomorrow
Target.ClearContents

End If
ws_exit:
Application.EnableEvents = True
End Sub

"Mark" wrote:

I am getting help, yet I need further help. The formula I post below, works
for B3 and to the right. I need it to work exactly the same from B3 thru B100
and have numbers in the entire row work as B3 does . That is B49 moves to
C49, C49 moves to D49 etc.

Do I need a code for each line? That would seem horribly wrong.
Thank you: Check this. Works great for one line B3. It's a great code

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address < "$B$3" Or _
IsEmpty(Target) Or _
Not IsNumeric(Target) Then
Exit Sub
End If
If MsgBox("Use the new value " & Target & _
" as new Daily Entry?", vbYesNo + vbDefaultButton1 _
+ vbInformation, "Verify Entry") < vbYes Then
Target.ClearContents
Exit Sub
End If
'overwrite E3 with D3
Range("E3") = Range("D3")
'overwrite D3 with C3
Range("D3") = Range("C3")
'overwrite C3 with B3
Range("C3") = Target
'clear B3 for tomorrow
Target.ClearContents
End Sub

Need B1:B100 to work this way

Sorry it didn't work : The code I have works acrros B3

It did not work in any cell between B4:b100

Your assistance is hugely appreciated

Mark

"Bernie Deitrick" wrote:

Mark,

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B1:B100")) Is Nothing Or _
IsEmpty(Target) Or _
Not IsNumeric(Target) Or _
Target.Cells.Count 1 Then
Exit Sub
End If
If MsgBox("Use the new value " & Target & _
" as new Daily Entry?", vbYesNo + vbDefaultButton1 _
+ vbInformation, "Verify Entry") < vbYes Then
Target.ClearContents
Exit Sub
End If
'overwrite old values
Application.EnableEvents = False
Target.Resize(1, 3).Cut Target.Offset(0, 1)
Application.EnableEvents = True

End Sub


HTH,
Bernie
MS Excel MVP


"Mark" wrote in message
...
I am getting help, yet I need further help. The formula I post below, works
for B3 and to the right. I need it to work exactly the same from B3 thru B100
and have numbers in the entire row work as B3 does . That is B49 moves to
C49, C49 moves to D49 etc.

Do I need a code for each line? That would seem horribly wrong.
Thank you: Check this. Works great for one line B3. It's a great code

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address < "$B$3" Or _
IsEmpty(Target) Or _
Not IsNumeric(Target) Then
Exit Sub
End If
If MsgBox("Use the new value " & Target & _
" as new Daily Entry?", vbYesNo + vbDefaultButton1 _
+ vbInformation, "Verify Entry") < vbYes Then
Target.ClearContents
Exit Sub
End If
'overwrite E3 with D3
Range("E3") = Range("D3")
'overwrite D3 with C3
Range("D3") = Range("C3")
'overwrite C3 with B3
Range("C3") = Target
'clear B3 for tomorrow
Target.ClearContents
End Sub

Need B1:B100 to work this way

Topper formula starts working down the line then quits working. When I go
back up to B# it doesn't ask "Yes" just stops.

Very close. I thought you had it...something stopped


"Toppers" wrote:

try:

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B1:B100"


On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
If IsEmpty(Target) Or _
Not IsNumeric(Target) Then
Exit Sub
End If
If MsgBox("Use the new value " & Target & _
" as new Daily Entry?", vbYesNo + vbDefaultButton1 _
+ vbInformation, "Verify Entry") < vbYes Then
Target.ClearContents
Exit Sub
End If
Row = Target.Row
Range("c" & Row).Resize(1, 2).Copy Range("d" & Row)
'overwrite C3 with B3
Range("C" & Row) = Target
'clear B3 for tomorrow
Target.ClearContents

End If
ws_exit:
Application.EnableEvents = True
End Sub

"Mark" wrote:

I am getting help, yet I need further help. The formula I post below, works
for B3 and to the right. I need it to work exactly the same from B3 thru B100
and have numbers in the entire row work as B3 does . That is B49 moves to
C49, C49 moves to D49 etc.

Do I need a code for each line? That would seem horribly wrong.
Thank you: Check this. Works great for one line B3. It's a great code

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address < "$B$3" Or _
IsEmpty(Target) Or _
Not IsNumeric(Target) Then
Exit Sub
End If
If MsgBox("Use the new value " & Target & _
" as new Daily Entry?", vbYesNo + vbDefaultButton1 _
+ vbInformation, "Verify Entry") < vbYes Then
Target.ClearContents
Exit Sub
End If
'overwrite E3 with D3
Range("E3") = Range("D3")
'overwrite D3 with C3
Range("D3") = Range("C3")
'overwrite C3 with B3
Range("C3") = Target
'clear B3 for tomorrow
Target.ClearContents
End Sub

Need B1:B100 to work this way

Sorry it didn't work : The code I have works acrros B3

It did not work in any cell between B4:b100

It does work, on every cell in the range B1:B100, as long as

1) you enter a value into a single cell
2) you enter a number

If you want to lift the single cell restriction, then remove


Or _
Target.Cells.Count 1

HTH,
Bernie
MS Excel MVP

Try:

Incoroprated some of Bernie's better code!

Private Sub Worksheet_Change(ByVal Target As Range)

Const WS_RANGE As String = "B1:B100"


On Error GoTo ws_exit:
Application.EnableEvents = False
If Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Or _
IsEmpty(Target) Or _
Not IsNumeric(Target) Then
GoTo ws_exit
End If
If MsgBox("Use the new value " & Target & _
" as new Daily Entry?", vbYesNo + vbDefaultButton1 _
+ vbInformation, "Verify Entry") < vbYes Then
Target.ClearContents
GoTo ws_exit
End If

Target.Resize(1, 3).Cut Target.Offset(0, 1)



ws_exit:
Application.EnableEvents = True
End Sub

"Mark" wrote:

Topper formula starts working down the line then quits working. When I go
back up to B# it doesn't ask "Yes" just stops.

Very close. I thought you had it...something stopped


"Toppers" wrote:

try:

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B1:B100"


On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
If IsEmpty(Target) Or _
Not IsNumeric(Target) Then
Exit Sub
End If
If MsgBox("Use the new value " & Target & _
" as new Daily Entry?", vbYesNo + vbDefaultButton1 _
+ vbInformation, "Verify Entry") < vbYes Then
Target.ClearContents
Exit Sub
End If
Row = Target.Row
Range("c" & Row).Resize(1, 2).Copy Range("d" & Row)
'overwrite C3 with B3
Range("C" & Row) = Target
'clear B3 for tomorrow
Target.ClearContents

End If
ws_exit:
Application.EnableEvents = True
End Sub

"Mark" wrote:

I am getting help, yet I need further help. The formula I post below, works
for B3 and to the right. I need it to work exactly the same from B3 thru B100
and have numbers in the entire row work as B3 does . That is B49 moves to
C49, C49 moves to D49 etc.

Do I need a code for each line? That would seem horribly wrong.
Thank you: Check this. Works great for one line B3. It's a great code

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address < "$B$3" Or _
IsEmpty(Target) Or _
Not IsNumeric(Target) Then
Exit Sub
End If
If MsgBox("Use the new value " & Target & _
" as new Daily Entry?", vbYesNo + vbDefaultButton1 _
+ vbInformation, "Verify Entry") < vbYes Then
Target.ClearContents
Exit Sub
End If
'overwrite E3 with D3
Range("E3") = Range("D3")
'overwrite D3 with C3
Range("D3") = Range("C3")
'overwrite C3 with B3
Range("C3") = Target
'clear B3 for tomorrow
Target.ClearContents
End Sub

Need B1:B100 to work this way

I enter a number in B3 sometimes down to B100

I need that # to go to C3, I need C3 to overide D3 and D3 ro overide e3,
basically continue to track the last 3 entries.

My formula only works for B3

Additional info F3 is =average(C3:E3)

"Bernie Deitrick" wrote:

Sorry it didn't work : The code I have works acrros B3

It did not work in any cell between B4:b100

It does work, on every cell in the range B1:B100, as long as

1) you enter a value into a single cell
2) you enter a number

If you want to lift the single cell restriction, then remove


Or _
Target.Cells.Count 1

HTH,
Bernie
MS Excel MVP

If you mean that whatever is entered in a cell in B1:B100 get put onto row 3 only, then you could
replace

Target.Resize(1, 3).Cut Target.Offset(0, 1)

with

Target.Copy
Range("C3").Insert Shift:=xlToRight
Range("F3").Clear 'This is optional
Target.Clear

HTH,
Bernie
MS Excel MVP


"Mark" wrote in message
...
I enter a number in B3 sometimes down to B100

I need that # to go to C3, I need C3 to overide D3 and D3 ro overide e3,
basically continue to track the last 3 entries.

My formula only works for B3

Additional info F3 is =average(C3:E3)

"Bernie Deitrick" wrote:

Sorry it didn't work : The code I have works acrros B3

It did not work in any cell between B4:b100

It does work, on every cell in the range B1:B100, as long as

1) you enter a value into a single cell
2) you enter a number

If you want to lift the single cell restriction, then remove


Or _
Target.Cells.Count 1

HTH,
Bernie
MS Excel MVP

DANG! BERNIE!

If I could give you more than one Perfect rating I would Thank you, Thank
You, Thank You

Question. I have a duplicate of this on sheet 2:

Is there anyway to merger the 2 sheets. That is ,as if I would copy (sheet2)
From A1 to J100 and paste it into sheet 1 around L1? Can you run both on one
formula or must the entire thing be redone?

Thanks anyway fanfriggin tastic!

Mark

"Bernie Deitrick" wrote:

Sorry it didn't work : The code I have works acrros B3

It did not work in any cell between B4:b100

It does work, on every cell in the range B1:B100, as long as

1) you enter a value into a single cell
2) you enter a number

If you want to lift the single cell restriction, then remove


Or _
Target.Cells.Count 1

HTH,
Bernie
MS Excel MVP

Perfect. I executed your code wrong. As I posted below. i would love to run 2
of these side by side on the same sheet devided by one column. Can it be done?

You guys are Fanatstic.

Mark

"Toppers" wrote:

try:

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B1:B100"


On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
If IsEmpty(Target) Or _
Not IsNumeric(Target) Then
Exit Sub
End If
If MsgBox("Use the new value " & Target & _
" as new Daily Entry?", vbYesNo + vbDefaultButton1 _
+ vbInformation, "Verify Entry") < vbYes Then
Target.ClearContents
Exit Sub
End If
Row = Target.Row
Range("c" & Row).Resize(1, 2).Copy Range("d" & Row)
'overwrite C3 with B3
Range("C" & Row) = Target
'clear B3 for tomorrow
Target.ClearContents

End If
ws_exit:
Application.EnableEvents = True
End Sub

"Mark" wrote:

I am getting help, yet I need further help. The formula I post below, works
for B3 and to the right. I need it to work exactly the same from B3 thru B100
and have numbers in the entire row work as B3 does . That is B49 moves to
C49, C49 moves to D49 etc.

Do I need a code for each line? That would seem horribly wrong.
Thank you: Check this. Works great for one line B3. It's a great code

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address < "$B$3" Or _
IsEmpty(Target) Or _
Not IsNumeric(Target) Then
Exit Sub
End If
If MsgBox("Use the new value " & Target & _
" as new Daily Entry?", vbYesNo + vbDefaultButton1 _
+ vbInformation, "Verify Entry") < vbYes Then
Target.ClearContents
Exit Sub
End If
'overwrite E3 with D3
Range("E3") = Range("D3")
'overwrite D3 with C3
Range("D3") = Range("C3")
'overwrite C3 with B3
Range("C3") = Target
'clear B3 for tomorrow
Target.ClearContents
End Sub

Need B1:B100 to work this way

try changing:

Target.Resize(1, 3).Cut Target.Offset(0, 1)

to

Target.Resize(1, 3).Copy Target.Offset(0, 1)
Target.ClearContents

HTH
"Mark" wrote:

I enter a number in B3 sometimes down to B100

I need that # to go to C3, I need C3 to overide D3 and D3 ro overide e3,
basically continue to track the last 3 entries.

My formula only works for B3

Additional info F3 is =average(C3:E3)

"Bernie Deitrick" wrote:

Sorry it didn't work : The code I have works acrros B3

It did not work in any cell between B4:b100

It does work, on every cell in the range B1:B100, as long as

1) you enter a value into a single cell
2) you enter a number

If you want to lift the single cell restriction, then remove


Or _
Target.Cells.Count 1

HTH,
Bernie
MS Excel MVP

To execute another range:

change to:

Const WS_RANGE As String = "B1:B100, L1:L100"

Date must be ENTERED into L1:L100 as copy/paste doesn't invoke the code.

"Mark" wrote:

DANG! BERNIE!

If I could give you more than one Perfect rating I would Thank you, Thank
You, Thank You

Question. I have a duplicate of this on sheet 2:

Is there anyway to merger the 2 sheets. That is ,as if I would copy (sheet2)
From A1 to J100 and paste it into sheet 1 around L1? Can you run both on one
formula or must the entire thing be redone?

Thanks anyway fanfriggin tastic!

Mark

"Bernie Deitrick" wrote:

Sorry it didn't work : The code I have works acrros B3

It did not work in any cell between B4:b100

It does work, on every cell in the range B1:B100, as long as

1) you enter a value into a single cell
2) you enter a number

If you want to lift the single cell restriction, then remove


Or _
Target.Cells.Count 1

HTH,
Bernie
MS Excel MVP

Ya know, I keep getting this message. "Macros can not be run because your
security levels are too high" I am set on medium. How do I make this go away

Mark

"Toppers" wrote:

To execute another range:

change to:

Const WS_RANGE As String = "B1:B100, L1:L100"

Date must be ENTERED into L1:L100 as copy/paste doesn't invoke the code.

"Mark" wrote:

DANG! BERNIE!

If I could give you more than one Perfect rating I would Thank you, Thank
You, Thank You

Question. I have a duplicate of this on sheet 2:

Is there anyway to merger the 2 sheets. That is ,as if I would copy (sheet2)
From A1 to J100 and paste it into sheet 1 around L1? Can you run both on one
formula or must the entire thing be redone?

Thanks anyway fanfriggin tastic!

Mark

"Bernie Deitrick" wrote:

Sorry it didn't work : The code I have works acrros B3

It did not work in any cell between B4:b100

It does work, on every cell in the range B1:B100, as long as

1) you enter a value into a single cell
2) you enter a number

If you want to lift the single cell restriction, then remove


Or _
Target.Cells.Count 1

HTH,
Bernie
MS Excel MVP

Waht do you mean date must be entered... Where"..Thanks.

Mark

"Toppers" wrote:

To execute another range:

change to:

Const WS_RANGE As String = "B1:B100, L1:L100"

Date must be ENTERED into L1:L100 as copy/paste doesn't invoke the code.

"Mark" wrote:

DANG! BERNIE!

If I could give you more than one Perfect rating I would Thank you, Thank
You, Thank You

Question. I have a duplicate of this on sheet 2:

Is there anyway to merger the 2 sheets. That is ,as if I would copy (sheet2)
From A1 to J100 and paste it into sheet 1 around L1? Can you run both on one
formula or must the entire thing be redone?

Thanks anyway fanfriggin tastic!

Mark

"Bernie Deitrick" wrote:

Sorry it didn't work : The code I have works acrros B3

It did not work in any cell between B4:b100

It does work, on every cell in the range B1:B100, as long as

1) you enter a value into a single cell
2) you enter a number

If you want to lift the single cell restriction, then remove


Or _
Target.Cells.Count 1

HTH,
Bernie
MS Excel MVP

I entered Const WS_RANGE As String = "B1:B100, L1:L100"

When I write to L3 it copies to C4

I sorta want the L3 group to write to L4 as B3 writes to B4

Almost there.. What wonderful help..

Thank you.

Mark

"Toppers" wrote:

To execute another range:

change to:

Const WS_RANGE As String = "B1:B100, L1:L100"

Date must be ENTERED into L1:L100 as copy/paste doesn't invoke the code.

"Mark" wrote:

DANG! BERNIE!

If I could give you more than one Perfect rating I would Thank you, Thank
You, Thank You

Question. I have a duplicate of this on sheet 2:

Is there anyway to merger the 2 sheets. That is ,as if I would copy (sheet2)
From A1 to J100 and paste it into sheet 1 around L1? Can you run both on one
formula or must the entire thing be redone?

Thanks anyway fanfriggin tastic!

Mark

"Bernie Deitrick" wrote:

Sorry it didn't work : The code I have works acrros B3

It did not work in any cell between B4:b100

It does work, on every cell in the range B1:B100, as long as

1) you enter a value into a single cell
2) you enter a number

If you want to lift the single cell restriction, then remove


Or _
Target.Cells.Count 1

HTH,
Bernie
MS Excel MVP

Use this code:

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B1:B100, L1:L100"


On Error GoTo ws_exit:
Application.EnableEvents = False
If Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Or _
IsEmpty(Target) Or _
Not IsNumeric(Target) Then
GoTo ws_exit
End If
If MsgBox("Use the new value " & Target & _
" as new Daily Entry?", vbYesNo + vbDefaultButton1 _
+ vbInformation, "Verify Entry") < vbYes Then
Target.ClearContents
GoTo ws_exit
End If

Target.Resize(1, 3).Copy Target.Offset(0, 1)
Target.Clear


ws_exit:
Application.EnableEvents = True
End Sub

"Mark" wrote:

I entered Const WS_RANGE As String = "B1:B100, L1:L100"

When I write to L3 it copies to C4

I sorta want the L3 group to write to L4 as B3 writes to B4

Almost there.. What wonderful help..

Thank you.

Mark

"Toppers" wrote:

To execute another range:

change to:

Const WS_RANGE As String = "B1:B100, L1:L100"

Date must be ENTERED into L1:L100 as copy/paste doesn't invoke the code.

"Mark" wrote:

DANG! BERNIE!

If I could give you more than one Perfect rating I would Thank you, Thank
You, Thank You

Question. I have a duplicate of this on sheet 2:

Is there anyway to merger the 2 sheets. That is ,as if I would copy (sheet2)
From A1 to J100 and paste it into sheet 1 around L1? Can you run both on one
formula or must the entire thing be redone?

Thanks anyway fanfriggin tastic!

Mark

"Bernie Deitrick" wrote:

Sorry it didn't work : The code I have works acrros B3

It did not work in any cell between B4:b100

It does work, on every cell in the range B1:B100, as long as

1) you enter a value into a single cell
2) you enter a number

If you want to lift the single cell restriction, then remove


Or _
Target.Cells.Count 1

HTH,
Bernie
MS Excel MVP

Topper, I am modified to 4:100 (across the board) had to put some titles in!!

Just friggin Brilliant! It may appear that I am freeloading, but, I am
passing along what you are helping me with:

QUESTION: to the code you sent!!!!: can we add.... In Column Q4:Q100 the +/-
amount. (Which is the +/- of the figure that ends up in M4:M100) ?

To clarify, when I enter a figure in L4:L100 and hit enter it all jumps
right. My last of the 3 entries in that area are in M column.

Q4:Q100 column is needed to show a +/- of (M4:M100)

Thanks for everything.

mark

"Toppers" wrote:

Use this code:

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B1:B100, L1:L100"


On Error GoTo ws_exit:
Application.EnableEvents = False
If Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Or _
IsEmpty(Target) Or _
Not IsNumeric(Target) Then
GoTo ws_exit
End If
If MsgBox("Use the new value " & Target & _
" as new Daily Entry?", vbYesNo + vbDefaultButton1 _
+ vbInformation, "Verify Entry") < vbYes Then
Target.ClearContents
GoTo ws_exit
End If

Target.Resize(1, 3).Copy Target.Offset(0, 1)
Target.Clear


ws_exit:
Application.EnableEvents = True
End Sub

"Mark" wrote:

I entered Const WS_RANGE As String = "B1:B100, L1:L100"

When I write to L3 it copies to C4

I sorta want the L3 group to write to L4 as B3 writes to B4

Almost there.. What wonderful help..

Thank you.

Mark

"Toppers" wrote:

To execute another range:

change to:

Const WS_RANGE As String = "B1:B100, L1:L100"

Date must be ENTERED into L1:L100 as copy/paste doesn't invoke the code.

"Mark" wrote:

DANG! BERNIE!

If I could give you more than one Perfect rating I would Thank you, Thank
You, Thank You

Question. I have a duplicate of this on sheet 2:

Is there anyway to merger the 2 sheets. That is ,as if I would copy (sheet2)
From A1 to J100 and paste it into sheet 1 around L1? Can you run both on one
formula or must the entire thing be redone?

Thanks anyway fanfriggin tastic!

Mark

"Bernie Deitrick" wrote:

Sorry it didn't work : The code I have works acrros B3

It did not work in any cell between B4:b100

It does work, on every cell in the range B1:B100, as long as

1) you enter a value into a single cell
2) you enter a number

If you want to lift the single cell restriction, then remove


Or _
Target.Cells.Count 1

HTH,
Bernie
MS Excel MVP

Mark,

After this line

Target.Clear

Add this:

If Target.Column = 12 Then
Cells(Target.Row,17).Value = _
Cells(Target.Row,17).Value + _
Cells(Target.Row,13).Value
End if


--
HTH,
Bernie
MS Excel MVP


"Mark" wrote in message
...
Topper, I am modified to 4:100 (across the board) had to put some titles in!!

Just friggin Brilliant! It may appear that I am freeloading, but, I am
passing along what you are helping me with:

QUESTION: to the code you sent!!!!: can we add.... In Column Q4:Q100 the +/-
amount. (Which is the +/- of the figure that ends up in M4:M100) ?

To clarify, when I enter a figure in L4:L100 and hit enter it all jumps
right. My last of the 3 entries in that area are in M column.

Q4:Q100 column is needed to show a +/- of (M4:M100)

Thanks for everything.

mark

"Toppers" wrote:

Use this code:

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B1:B100, L1:L100"


On Error GoTo ws_exit:
Application.EnableEvents = False
If Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Or _
IsEmpty(Target) Or _
Not IsNumeric(Target) Then
GoTo ws_exit
End If
If MsgBox("Use the new value " & Target & _
" as new Daily Entry?", vbYesNo + vbDefaultButton1 _
+ vbInformation, "Verify Entry") < vbYes Then
Target.ClearContents
GoTo ws_exit
End If

Target.Resize(1, 3).Copy Target.Offset(0, 1)
Target.Clear


ws_exit:
Application.EnableEvents = True
End Sub

"Mark" wrote:

I entered Const WS_RANGE As String = "B1:B100, L1:L100"

When I write to L3 it copies to C4

I sorta want the L3 group to write to L4 as B3 writes to B4

Almost there.. What wonderful help..

Thank you.

Mark

"Toppers" wrote:

To execute another range:

change to:

Const WS_RANGE As String = "B1:B100, L1:L100"

Date must be ENTERED into L1:L100 as copy/paste doesn't invoke the code.

"Mark" wrote:

DANG! BERNIE!

If I could give you more than one Perfect rating I would Thank you, Thank
You, Thank You

Question. I have a duplicate of this on sheet 2:

Is there anyway to merger the 2 sheets. That is ,as if I would copy (sheet2)
From A1 to J100 and paste it into sheet 1 around L1? Can you run both on one
formula or must the entire thing be redone?

Thanks anyway fanfriggin tastic!

Mark

"Bernie Deitrick" wrote:

Sorry it didn't work : The code I have works acrros B3

It did not work in any cell between B4:b100

It does work, on every cell in the range B1:B100, as long as

1) you enter a value into a single cell
2) you enter a number

If you want to lift the single cell restriction, then remove


Or _
Target.Cells.Count 1

HTH,
Bernie
MS Excel MVP

Bernie, Thank you. I screwed this all up. Let me see if I can un-FOG my brain.

In you guys MVP's formula ..I will repost I would like this:

In my formula I would like:

I messed this up, mostly because I have made a change.. Please let me restate:


I would like M Column M4:M100 to be the +/- the numbers down I4:I100 "I"
column is driven by what I type in H.

L is the target L4:100

ie: If L20 is 25 and I4 is 18 then M4 is +7 or 7
If L4 is 20 and I4 is 14 then M4 is -6

AS I4:I100 gets a new #, L changes. (it's and average)

Can you help me make (M4:100) be +/-) I column)
I would like the old M4:M100 to move to N4:N100,
Then the old N4:N100, to move to O4:O100,
and Old O4:O100 drops off.

My formula is this presently:
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B4:B100, H4:H100"


On Error GoTo ws_exit:
Application.EnableEvents = False
If Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Or _
IsEmpty(Target) Or _
Not IsNumeric(Target) Then
GoTo ws_exit
End If
If MsgBox("Use the new value " & Target & _
" as new Daily Entry?", vbYesNo + vbDefaultButton1 _
+ vbInformation, "Verify Entry") < vbYes Then

GoTo ws_exit


End If

Target.Resize(1, 3).Copy Target.Offset(0, 1)
Target.Clear

If Target.Column = 12 Then
Cells(Target.Row, 17).Value = _
Cells(Target.Row, 17).Value + _
Cells(Target.Row, 13).Value
End If


ws_exit:
Application.EnableEvents = True
End Sub


Thank you Mark



I see no action in it may be because I have compressed the sheet in to fit
on on portrait page and I did not correct where you need to hit the target.

May I please repeat.



"Bernie Deitrick" wrote:

Mark,

After this line

Target.Clear

Add this:

If Target.Column = 12 Then
Cells(Target.Row,17).Value = _
Cells(Target.Row,17).Value + _
Cells(Target.Row,13).Value
End if


--
HTH,
Bernie
MS Excel MVP


"Mark" wrote in message
...
Topper, I am modified to 4:100 (across the board) had to put some titles in!!

Just friggin Brilliant! It may appear that I am freeloading, but, I am
passing along what you are helping me with:

QUESTION: to the code you sent!!!!: can we add.... In Column Q4:Q100 the +/-
amount. (Which is the +/- of the figure that ends up in M4:M100) ?

To clarify, when I enter a figure in L4:L100 and hit enter it all jumps
right. My last of the 3 entries in that area are in M column.

Q4:Q100 column is needed to show a +/- of (M4:M100)

Thanks for everything.

mark

"Toppers" wrote:

Use this code:

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B1:B100, L1:L100"


On Error GoTo ws_exit:
Application.EnableEvents = False
If Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Or _
IsEmpty(Target) Or _
Not IsNumeric(Target) Then
GoTo ws_exit
End If
If MsgBox("Use the new value " & Target & _
" as new Daily Entry?", vbYesNo + vbDefaultButton1 _
+ vbInformation, "Verify Entry") < vbYes Then
Target.ClearContents
GoTo ws_exit
End If

Target.Resize(1, 3).Copy Target.Offset(0, 1)
Target.Clear


ws_exit:
Application.EnableEvents = True
End Sub

"Mark" wrote:

I entered Const WS_RANGE As String = "B1:B100, L1:L100"

When I write to L3 it copies to C4

I sorta want the L3 group to write to L4 as B3 writes to B4

Almost there.. What wonderful help..

Thank you.

Mark

"Toppers" wrote:

To execute another range:

change to:

Const WS_RANGE As String = "B1:B100, L1:L100"

Date must be ENTERED into L1:L100 as copy/paste doesn't invoke the code.

"Mark" wrote:

DANG! BERNIE!

If I could give you more than one Perfect rating I would Thank you, Thank
You, Thank You

Question. I have a duplicate of this on sheet 2:

Is there anyway to merger the 2 sheets. That is ,as if I would copy (sheet2)
From A1 to J100 and paste it into sheet 1 around L1? Can you run both on one
formula or must the entire thing be redone?

Thanks anyway fanfriggin tastic!

Mark

"Bernie Deitrick" wrote:

Sorry it didn't work : The code I have works acrros B3

It did not work in any cell between B4:b100

It does work, on every cell in the range B1:B100, as long as

1) you enter a value into a single cell
2) you enter a number

If you want to lift the single cell restriction, then remove


Or _
Target.Cells.Count 1

HTH,
Bernie
MS Excel MVP

Mark,

ie: If L20 is 25 and I4 is 18 then M4 is +7 or 7
If L4 is 20 and I4 is 14 then M4 is -6


Where are you getting the +7 / - 6 ?

I'm still not sure what you want.

L is column 12.
M is column 13.
Q is column 17.
Target is the cell that was changed.

This code means:

' only do this is the cell that changed is in column L
If Target.Column = 12 Then
' Change the value in column Q
' by taking the original value in column Q
' and adding the value in column M
' all in the same row as the changed cell
Cells(Target.Row, 17).Value = _
Cells(Target.Row, 17).Value + _
Cells(Target.Row, 13).Value
End If

give an example of values in one row, and what you would type, with before and after values....

HTH,
Bernie
MS Excel MVP


"Mark" wrote in message
...
Bernie, Thank you. I screwed this all up. Let me see if I can un-FOG my brain.

In you guys MVP's formula ..I will repost I would like this:

In my formula I would like:

I messed this up, mostly because I have made a change.. Please let me restate:


I would like M Column M4:M100 to be the +/- the numbers down I4:I100 "I"
column is driven by what I type in H.

L is the target L4:100

ie: If L20 is 25 and I4 is 18 then M4 is +7 or 7
If L4 is 20 and I4 is 14 then M4 is -6

AS I4:I100 gets a new #, L changes. (it's and average)

Can you help me make (M4:100) be +/-) I column)
I would like the old M4:M100 to move to N4:N100,
Then the old N4:N100, to move to O4:O100,
and Old O4:O100 drops off.

My formula is this presently:
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B4:B100, H4:H100"


On Error GoTo ws_exit:
Application.EnableEvents = False
If Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Or _
IsEmpty(Target) Or _
Not IsNumeric(Target) Then
GoTo ws_exit
End If
If MsgBox("Use the new value " & Target & _
" as new Daily Entry?", vbYesNo + vbDefaultButton1 _
+ vbInformation, "Verify Entry") < vbYes Then

GoTo ws_exit


End If

Target.Resize(1, 3).Copy Target.Offset(0, 1)
Target.Clear

If Target.Column = 12 Then
Cells(Target.Row, 17).Value = _
Cells(Target.Row, 17).Value + _
Cells(Target.Row, 13).Value
End If


ws_exit:
Application.EnableEvents = True
End Sub


Thank you Mark



I see no action in it may be because I have compressed the sheet in to fit
on on portrait page and I did not correct where you need to hit the target.

May I please repeat.



"Bernie Deitrick" wrote:

Mark,

After this line

Target.Clear

Add this:

If Target.Column = 12 Then
Cells(Target.Row,17).Value = _
Cells(Target.Row,17).Value + _
Cells(Target.Row,13).Value
End if


--
HTH,
Bernie
MS Excel MVP


"Mark" wrote in message
...
Topper, I am modified to 4:100 (across the board) had to put some titles in!!

Just friggin Brilliant! It may appear that I am freeloading, but, I am
passing along what you are helping me with:

QUESTION: to the code you sent!!!!: can we add.... In Column Q4:Q100 the +/-
amount. (Which is the +/- of the figure that ends up in M4:M100) ?

To clarify, when I enter a figure in L4:L100 and hit enter it all jumps
right. My last of the 3 entries in that area are in M column.

Q4:Q100 column is needed to show a +/- of (M4:M100)

Thanks for everything.

mark

"Toppers" wrote:

Use this code:

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B1:B100, L1:L100"


On Error GoTo ws_exit:
Application.EnableEvents = False
If Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Or _
IsEmpty(Target) Or _
Not IsNumeric(Target) Then
GoTo ws_exit
End If
If MsgBox("Use the new value " & Target & _
" as new Daily Entry?", vbYesNo + vbDefaultButton1 _
+ vbInformation, "Verify Entry") < vbYes Then
Target.ClearContents
GoTo ws_exit
End If

Target.Resize(1, 3).Copy Target.Offset(0, 1)
Target.Clear


ws_exit:
Application.EnableEvents = True
End Sub

"Mark" wrote:

I entered Const WS_RANGE As String = "B1:B100, L1:L100"

When I write to L3 it copies to C4

I sorta want the L3 group to write to L4 as B3 writes to B4

Almost there.. What wonderful help..

Thank you.

Mark

"Toppers" wrote:

To execute another range:

change to:

Const WS_RANGE As String = "B1:B100, L1:L100"

Date must be ENTERED into L1:L100 as copy/paste doesn't invoke the code.

"Mark" wrote:

DANG! BERNIE!

If I could give you more than one Perfect rating I would Thank you, Thank
You, Thank You

Question. I have a duplicate of this on sheet 2:

Is there anyway to merger the 2 sheets. That is ,as if I would copy (sheet2)
From A1 to J100 and paste it into sheet 1 around L1? Can you run both on one
formula or must the entire thing be redone?

Thanks anyway fanfriggin tastic!

Mark

"Bernie Deitrick" wrote:

Sorry it didn't work : The code I have works acrros B3

It did not work in any cell between B4:b100

It does work, on every cell in the range B1:B100, as long as

1) you enter a value into a single cell
2) you enter a number

If you want to lift the single cell restriction, then remove


Or _
Target.Cells.Count 1

HTH,
Bernie
MS Excel MVP

I'm truly sorry Bernie, Please don't give up on me., I would hate to aliente
such wonderful help.

In my code: L column is an average of I,J,k
=AVERAGE(I4:K4)
If L is the target, whatever # is in "I" (not the average) the actual # in
"I" minus L goes in M.

I4 = 18, L4 = 24, M changes to -6
I4 = 28, L4 = 10, M change to +18 or 18

thats the first step.

At present I am getting some sort of average in M. I think it is because of
the code. I don'y know. When I4 is 16 and L4 is 18, I'm getting 1.7

go figure.

Thank you
mark

"Bernie Deitrick" wrote:

Mark,

ie: If L20 is 25 and I4 is 18 then M4 is +7 or 7
If L4 is 20 and I4 is 14 then M4 is -6


Where are you getting the +7 / - 6 ?

I'm still not sure what you want.

L is column 12.
M is column 13.
Q is column 17.
Target is the cell that was changed.

This code means:

' only do this is the cell that changed is in column L
If Target.Column = 12 Then
' Change the value in column Q
' by taking the original value in column Q
' and adding the value in column M
' all in the same row as the changed cell
Cells(Target.Row, 17).Value = _
Cells(Target.Row, 17).Value + _
Cells(Target.Row, 13).Value
End If

give an example of values in one row, and what you would type, with before and after values....

HTH,
Bernie
MS Excel MVP


"Mark" wrote in message
...
Bernie, Thank you. I screwed this all up. Let me see if I can un-FOG my brain.

In you guys MVP's formula ..I will repost I would like this:

In my formula I would like:

I messed this up, mostly because I have made a change.. Please let me restate:


I would like M Column M4:M100 to be the +/- the numbers down I4:I100 "I"
column is driven by what I type in H.

L is the target L4:100

ie: If L20 is 25 and I4 is 18 then M4 is +7 or 7
If L4 is 20 and I4 is 14 then M4 is -6

AS I4:I100 gets a new #, L changes. (it's and average)

Can you help me make (M4:100) be +/-) I column)
I would like the old M4:M100 to move to N4:N100,
Then the old N4:N100, to move to O4:O100,
and Old O4:O100 drops off.

My formula is this presently:
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B4:B100, H4:H100"


On Error GoTo ws_exit:
Application.EnableEvents = False
If Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Or _
IsEmpty(Target) Or _
Not IsNumeric(Target) Then
GoTo ws_exit
End If
If MsgBox("Use the new value " & Target & _
" as new Daily Entry?", vbYesNo + vbDefaultButton1 _
+ vbInformation, "Verify Entry") < vbYes Then

GoTo ws_exit


End If

Target.Resize(1, 3).Copy Target.Offset(0, 1)
Target.Clear

If Target.Column = 12 Then
Cells(Target.Row, 17).Value = _
Cells(Target.Row, 17).Value + _
Cells(Target.Row, 13).Value
End If


ws_exit:
Application.EnableEvents = True
End Sub


Thank you Mark



I see no action in it may be because I have compressed the sheet in to fit
on on portrait page and I did not correct where you need to hit the target.

May I please repeat.



"Bernie Deitrick" wrote:

Mark,

After this line

Target.Clear

Add this:

If Target.Column = 12 Then
Cells(Target.Row,17).Value = _
Cells(Target.Row,17).Value + _
Cells(Target.Row,13).Value
End if


--
HTH,
Bernie
MS Excel MVP


"Mark" wrote in message
...
Topper, I am modified to 4:100 (across the board) had to put some titles in!!

Just friggin Brilliant! It may appear that I am freeloading, but, I am
passing along what you are helping me with:

QUESTION: to the code you sent!!!!: can we add.... In Column Q4:Q100 the +/-
amount. (Which is the +/- of the figure that ends up in M4:M100) ?

To clarify, when I enter a figure in L4:L100 and hit enter it all jumps
right. My last of the 3 entries in that area are in M column.

Q4:Q100 column is needed to show a +/- of (M4:M100)

Thanks for everything.

mark

"Toppers" wrote:

Use this code:

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B1:B100, L1:L100"


On Error GoTo ws_exit:
Application.EnableEvents = False
If Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Or _
IsEmpty(Target) Or _
Not IsNumeric(Target) Then
GoTo ws_exit
End If
If MsgBox("Use the new value " & Target & _
" as new Daily Entry?", vbYesNo + vbDefaultButton1 _
+ vbInformation, "Verify Entry") < vbYes Then
Target.ClearContents
GoTo ws_exit
End If

Target.Resize(1, 3).Copy Target.Offset(0, 1)
Target.Clear


ws_exit:
Application.EnableEvents = True
End Sub

"Mark" wrote:

I entered Const WS_RANGE As String = "B1:B100, L1:L100"

When I write to L3 it copies to C4

I sorta want the L3 group to write to L4 as B3 writes to B4

Almost there.. What wonderful help..

Thank you.

Mark

"Toppers" wrote:

To execute another range:

change to:

Const WS_RANGE As String = "B1:B100, L1:L100"

Date must be ENTERED into L1:L100 as copy/paste doesn't invoke the code.

"Mark" wrote:

DANG! BERNIE!

If I could give you more than one Perfect rating I would Thank you, Thank
You, Thank You

Question. I have a duplicate of this on sheet 2:

Is there anyway to merger the 2 sheets. That is ,as if I would copy (sheet2)
From A1 to J100 and paste it into sheet 1 around L1? Can you run both on one
formula or must the entire thing be redone?

Thanks anyway fanfriggin tastic!

Mark

"Bernie Deitrick" wrote:

Sorry it didn't work : The code I have works acrros B3

It did not work in any cell between B4:b100

It does work, on every cell in the range B1:B100, as long as

1) you enter a value into a single cell
2) you enter a number

If you want to lift the single cell restriction, then remove


Or _
Target.Cells.Count 1

HTH,
Bernie
MS Excel MVP

Mark,

You're not going to alienate us, ever, unless you start abusing us.

What column are you actually changing? Since L has a formula, you probably aren't typing any values
in that column. Or are you?

Why not use the formula =L4-I4 in cell M4?

Still not sure what you are doing...

HTH,
Bernie
MS Excel MVP


"Mark" wrote in message
...

I'm truly sorry Bernie, Please don't give up on me., I would hate to aliente
such wonderful help.

In my code: L column is an average of I,J,k
=AVERAGE(I4:K4)
If L is the target, whatever # is in "I" (not the average) the actual # in
"I" minus L goes in M.

I4 = 18, L4 = 24, M changes to -6
I4 = 28, L4 = 10, M change to +18 or 18

thats the first step.

At present I am getting some sort of average in M. I think it is because of
the code. I don'y know. When I4 is 16 and L4 is 18, I'm getting 1.7

go figure.

Thank you
mark

"Bernie Deitrick" wrote:

Mark,

ie: If L20 is 25 and I4 is 18 then M4 is +7 or 7
If L4 is 20 and I4 is 14 then M4 is -6


Where are you getting the +7 / - 6 ?

I'm still not sure what you want.

L is column 12.
M is column 13.
Q is column 17.
Target is the cell that was changed.

This code means:

' only do this is the cell that changed is in column L
If Target.Column = 12 Then
' Change the value in column Q
' by taking the original value in column Q
' and adding the value in column M
' all in the same row as the changed cell
Cells(Target.Row, 17).Value = _
Cells(Target.Row, 17).Value + _
Cells(Target.Row, 13).Value
End If

give an example of values in one row, and what you would type, with before and after values....

HTH,
Bernie
MS Excel MVP


"Mark" wrote in message
...
Bernie, Thank you. I screwed this all up. Let me see if I can un-FOG my brain.

In you guys MVP's formula ..I will repost I would like this:

In my formula I would like:

I messed this up, mostly because I have made a change.. Please let me restate:


I would like M Column M4:M100 to be the +/- the numbers down I4:I100 "I"
column is driven by what I type in H.

L is the target L4:100

ie: If L20 is 25 and I4 is 18 then M4 is +7 or 7
If L4 is 20 and I4 is 14 then M4 is -6

AS I4:I100 gets a new #, L changes. (it's and average)

Can you help me make (M4:100) be +/-) I column)
I would like the old M4:M100 to move to N4:N100,
Then the old N4:N100, to move to O4:O100,
and Old O4:O100 drops off.

My formula is this presently:
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B4:B100, H4:H100"


On Error GoTo ws_exit:
Application.EnableEvents = False
If Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Or _
IsEmpty(Target) Or _
Not IsNumeric(Target) Then
GoTo ws_exit
End If
If MsgBox("Use the new value " & Target & _
" as new Daily Entry?", vbYesNo + vbDefaultButton1 _
+ vbInformation, "Verify Entry") < vbYes Then

GoTo ws_exit


End If

Target.Resize(1, 3).Copy Target.Offset(0, 1)
Target.Clear

If Target.Column = 12 Then
Cells(Target.Row, 17).Value = _
Cells(Target.Row, 17).Value + _
Cells(Target.Row, 13).Value
End If


ws_exit:
Application.EnableEvents = True
End Sub


Thank you Mark



I see no action in it may be because I have compressed the sheet in to fit
on on portrait page and I did not correct where you need to hit the target.

May I please repeat.



"Bernie Deitrick" wrote:

Mark,

After this line

Target.Clear

Add this:

If Target.Column = 12 Then
Cells(Target.Row,17).Value = _
Cells(Target.Row,17).Value + _
Cells(Target.Row,13).Value
End if


--
HTH,
Bernie
MS Excel MVP


"Mark" wrote in message
...
Topper, I am modified to 4:100 (across the board) had to put some titles in!!

Just friggin Brilliant! It may appear that I am freeloading, but, I am
passing along what you are helping me with:

QUESTION: to the code you sent!!!!: can we add.... In Column Q4:Q100 the +/-
amount. (Which is the +/- of the figure that ends up in M4:M100) ?

To clarify, when I enter a figure in L4:L100 and hit enter it all jumps
right. My last of the 3 entries in that area are in M column.

Q4:Q100 column is needed to show a +/- of (M4:M100)

Thanks for everything.

mark

"Toppers" wrote:

Use this code:

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B1:B100, L1:L100"


On Error GoTo ws_exit:
Application.EnableEvents = False
If Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Or _
IsEmpty(Target) Or _
Not IsNumeric(Target) Then
GoTo ws_exit
End If
If MsgBox("Use the new value " & Target & _
" as new Daily Entry?", vbYesNo + vbDefaultButton1 _
+ vbInformation, "Verify Entry") < vbYes Then
Target.ClearContents
GoTo ws_exit
End If

Target.Resize(1, 3).Copy Target.Offset(0, 1)
Target.Clear


ws_exit:
Application.EnableEvents = True
End Sub

"Mark" wrote:

I entered Const WS_RANGE As String = "B1:B100, L1:L100"

When I write to L3 it copies to C4

I sorta want the L3 group to write to L4 as B3 writes to B4

Almost there.. What wonderful help..

Thank you.

Mark

"Toppers" wrote:

To execute another range:

change to:

Const WS_RANGE As String = "B1:B100, L1:L100"

Date must be ENTERED into L1:L100 as copy/paste doesn't invoke the code.

"Mark" wrote:

DANG! BERNIE!

If I could give you more than one Perfect rating I would Thank you, Thank
You, Thank You

Question. I have a duplicate of this on sheet 2:

Is there anyway to merger the 2 sheets. That is ,as if I would copy (sheet2)
From A1 to J100 and paste it into sheet 1 around L1? Can you run both on one
formula or must the entire thing be redone?

Thanks anyway fanfriggin tastic!

Mark

"Bernie Deitrick" wrote:

Sorry it didn't work : The code I have works acrros B3

It did not work in any cell between B4:b100

It does work, on every cell in the range B1:B100, as long as

1) you enter a value into a single cell
2) you enter a number

If you want to lift the single cell restriction, then remove


Or _
Target.Cells.Count 1

HTH,
Bernie
MS Excel MVP

ok fixed that..

Went in and changed decimal to none. It is rounding on its own I guess.

2nd step.

As a new number comes into I4 Assuming (I4:I100) L changes (because it is
an average of I,J,K.
M gets the +/-

All good.

When M gets a new total
I would like Old M to move to N
Old N to O
Old O move to trash.

Looks Like this:

M N O
-4 6 3 to:

M N O
2 -4 6

M N O
-3 2 -4



"Bernie Deitrick" wrote:

Mark,

ie: If L20 is 25 and I4 is 18 then M4 is +7 or 7
If L4 is 20 and I4 is 14 then M4 is -6


Where are you getting the +7 / - 6 ?

I'm still not sure what you want.

L is column 12.
M is column 13.
Q is column 17.
Target is the cell that was changed.

This code means:

' only do this is the cell that changed is in column L
If Target.Column = 12 Then
' Change the value in column Q
' by taking the original value in column Q
' and adding the value in column M
' all in the same row as the changed cell
Cells(Target.Row, 17).Value = _
Cells(Target.Row, 17).Value + _
Cells(Target.Row, 13).Value
End If

give an example of values in one row, and what you would type, with before and after values....

HTH,
Bernie
MS Excel MVP


"Mark" wrote in message
...
Bernie, Thank you. I screwed this all up. Let me see if I can un-FOG my brain.

In you guys MVP's formula ..I will repost I would like this:

In my formula I would like:

I messed this up, mostly because I have made a change.. Please let me restate:


I would like M Column M4:M100 to be the +/- the numbers down I4:I100 "I"
column is driven by what I type in H.

L is the target L4:100

ie: If L20 is 25 and I4 is 18 then M4 is +7 or 7
If L4 is 20 and I4 is 14 then M4 is -6

AS I4:I100 gets a new #, L changes. (it's and average)

Can you help me make (M4:100) be +/-) I column)
I would like the old M4:M100 to move to N4:N100,
Then the old N4:N100, to move to O4:O100,
and Old O4:O100 drops off.

My formula is this presently:
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B4:B100, H4:H100"


On Error GoTo ws_exit:
Application.EnableEvents = False
If Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Or _
IsEmpty(Target) Or _
Not IsNumeric(Target) Then
GoTo ws_exit
End If
If MsgBox("Use the new value " & Target & _
" as new Daily Entry?", vbYesNo + vbDefaultButton1 _
+ vbInformation, "Verify Entry") < vbYes Then

GoTo ws_exit


End If

Target.Resize(1, 3).Copy Target.Offset(0, 1)
Target.Clear

If Target.Column = 12 Then
Cells(Target.Row, 17).Value = _
Cells(Target.Row, 17).Value + _
Cells(Target.Row, 13).Value
End If


ws_exit:
Application.EnableEvents = True
End Sub


Thank you Mark



I see no action in it may be because I have compressed the sheet in to fit
on on portrait page and I did not correct where you need to hit the target.

May I please repeat.



"Bernie Deitrick" wrote:

Mark,

After this line

Target.Clear

Add this:

If Target.Column = 12 Then
Cells(Target.Row,17).Value = _
Cells(Target.Row,17).Value + _
Cells(Target.Row,13).Value
End if


--
HTH,
Bernie
MS Excel MVP


"Mark" wrote in message
...
Topper, I am modified to 4:100 (across the board) had to put some titles in!!

Just friggin Brilliant! It may appear that I am freeloading, but, I am
passing along what you are helping me with:

QUESTION: to the code you sent!!!!: can we add.... In Column Q4:Q100 the +/-
amount. (Which is the +/- of the figure that ends up in M4:M100) ?

To clarify, when I enter a figure in L4:L100 and hit enter it all jumps
right. My last of the 3 entries in that area are in M column.

Q4:Q100 column is needed to show a +/- of (M4:M100)

Thanks for everything.

mark

"Toppers" wrote:

Use this code:

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B1:B100, L1:L100"


On Error GoTo ws_exit:
Application.EnableEvents = False
If Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Or _
IsEmpty(Target) Or _
Not IsNumeric(Target) Then
GoTo ws_exit
End If
If MsgBox("Use the new value " & Target & _
" as new Daily Entry?", vbYesNo + vbDefaultButton1 _
+ vbInformation, "Verify Entry") < vbYes Then
Target.ClearContents
GoTo ws_exit
End If

Target.Resize(1, 3).Copy Target.Offset(0, 1)
Target.Clear


ws_exit:
Application.EnableEvents = True
End Sub

"Mark" wrote:

I entered Const WS_RANGE As String = "B1:B100, L1:L100"

When I write to L3 it copies to C4

I sorta want the L3 group to write to L4 as B3 writes to B4

Almost there.. What wonderful help..

Thank you.

Mark

"Toppers" wrote:

To execute another range:

change to:

Const WS_RANGE As String = "B1:B100, L1:L100"

Date must be ENTERED into L1:L100 as copy/paste doesn't invoke the code.

"Mark" wrote:

DANG! BERNIE!

If I could give you more than one Perfect rating I would Thank you, Thank
You, Thank You

Question. I have a duplicate of this on sheet 2:

Is there anyway to merger the 2 sheets. That is ,as if I would copy (sheet2)
From A1 to J100 and paste it into sheet 1 around L1? Can you run both on one
formula or must the entire thing be redone?

Thanks anyway fanfriggin tastic!

Mark

"Bernie Deitrick" wrote:

Sorry it didn't work : The code I have works acrros B3

It did not work in any cell between B4:b100

It does work, on every cell in the range B1:B100, as long as

1) you enter a value into a single cell
2) you enter a number

If you want to lift the single cell restriction, then remove


Or _
Target.Cells.Count 1

HTH,
Bernie
MS Excel MVP

On my sheet:

I enter a number in H (assuming I have 100 lines) it asks me to confirm and
when I say yes it moves to "I". The Old I moves to J, J to K and "L" is the
average of I,J,K.

M is now key! =(I4-L4)

As M changes. (with an entry into H)
I would like the old or M to move to N, N to O, and Old O drop off.

In M,N,O I am trying to track, the last 3 + 0r minus's. They shift right. As
does I,J,K when I put something in H.

Did I confuse the issue more. I'll stop. and try agin later.

mark, Thank you

My code:
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B4:B100, H4:H100"


On Error GoTo ws_exit:
Application.EnableEvents = False
If Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Or _
IsEmpty(Target) Or _
Not IsNumeric(Target) Then
GoTo ws_exit
End If
If MsgBox("Use the new value " & Target & _
" as new Daily Entry?", vbYesNo + vbDefaultButton1 _
+ vbInformation, "Verify Entry") < vbYes Then

GoTo ws_exit


End If

Target.Resize(1, 3).Copy Target.Offset(0, 1)
Target.Clear


ws_exit:
Application.EnableEvents = True
End Sub




"Bernie Deitrick" wrote:

Mark,

You're not going to alienate us, ever, unless you start abusing us.

What column are you actually changing? Since L has a formula, you probably aren't typing any values
in that column. Or are you?

Why not use the formula =L4-I4 in cell M4?

Still not sure what you are doing...

HTH,
Bernie
MS Excel MVP


"Mark" wrote in message
...

I'm truly sorry Bernie, Please don't give up on me., I would hate to aliente
such wonderful help.

In my code: L column is an average of I,J,k
=AVERAGE(I4:K4)
If L is the target, whatever # is in "I" (not the average) the actual # in
"I" minus L goes in M.

I4 = 18, L4 = 24, M changes to -6
I4 = 28, L4 = 10, M change to +18 or 18

thats the first step.

At present I am getting some sort of average in M. I think it is because of
the code. I don'y know. When I4 is 16 and L4 is 18, I'm getting 1.7

go figure.

Thank you
mark

"Bernie Deitrick" wrote:

Mark,

ie: If L20 is 25 and I4 is 18 then M4 is +7 or 7
If L4 is 20 and I4 is 14 then M4 is -6


Where are you getting the +7 / - 6 ?

I'm still not sure what you want.

L is column 12.
M is column 13.
Q is column 17.
Target is the cell that was changed.

This code means:

' only do this is the cell that changed is in column L
If Target.Column = 12 Then
' Change the value in column Q
' by taking the original value in column Q
' and adding the value in column M
' all in the same row as the changed cell
Cells(Target.Row, 17).Value = _
Cells(Target.Row, 17).Value + _
Cells(Target.Row, 13).Value
End If

give an example of values in one row, and what you would type, with before and after values....

HTH,
Bernie
MS Excel MVP


"Mark" wrote in message
...
Bernie, Thank you. I screwed this all up. Let me see if I can un-FOG my brain.

In you guys MVP's formula ..I will repost I would like this:

In my formula I would like:

I messed this up, mostly because I have made a change.. Please let me restate:


I would like M Column M4:M100 to be the +/- the numbers down I4:I100 "I"
column is driven by what I type in H.

L is the target L4:100

ie: If L20 is 25 and I4 is 18 then M4 is +7 or 7
If L4 is 20 and I4 is 14 then M4 is -6

AS I4:I100 gets a new #, L changes. (it's and average)

Can you help me make (M4:100) be +/-) I column)
I would like the old M4:M100 to move to N4:N100,
Then the old N4:N100, to move to O4:O100,
and Old O4:O100 drops off.

My formula is this presently:
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B4:B100, H4:H100"


On Error GoTo ws_exit:
Application.EnableEvents = False
If Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Or _
IsEmpty(Target) Or _
Not IsNumeric(Target) Then
GoTo ws_exit
End If
If MsgBox("Use the new value " & Target & _
" as new Daily Entry?", vbYesNo + vbDefaultButton1 _
+ vbInformation, "Verify Entry") < vbYes Then

GoTo ws_exit


End If

Target.Resize(1, 3).Copy Target.Offset(0, 1)
Target.Clear

If Target.Column = 12 Then
Cells(Target.Row, 17).Value = _
Cells(Target.Row, 17).Value + _
Cells(Target.Row, 13).Value
End If


ws_exit:
Application.EnableEvents = True
End Sub


Thank you Mark



I see no action in it may be because I have compressed the sheet in to fit
on on portrait page and I did not correct where you need to hit the target.

May I please repeat.



"Bernie Deitrick" wrote:

Mark,

After this line

Target.Clear

Add this:

If Target.Column = 12 Then
Cells(Target.Row,17).Value = _
Cells(Target.Row,17).Value + _
Cells(Target.Row,13).Value
End if


--
HTH,
Bernie
MS Excel MVP


"Mark" wrote in message
...
Topper, I am modified to 4:100 (across the board) had to put some titles in!!

Just friggin Brilliant! It may appear that I am freeloading, but, I am
passing along what you are helping me with:

QUESTION: to the code you sent!!!!: can we add.... In Column Q4:Q100 the +/-
amount. (Which is the +/- of the figure that ends up in M4:M100) ?

To clarify, when I enter a figure in L4:L100 and hit enter it all jumps
right. My last of the 3 entries in that area are in M column.

Q4:Q100 column is needed to show a +/- of (M4:M100)

Thanks for everything.

mark

"Toppers" wrote:

Use this code:

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B1:B100, L1:L100"


On Error GoTo ws_exit:
Application.EnableEvents = False
If Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Or _
IsEmpty(Target) Or _
Not IsNumeric(Target) Then
GoTo ws_exit
End If
If MsgBox("Use the new value " & Target & _
" as new Daily Entry?", vbYesNo + vbDefaultButton1 _
+ vbInformation, "Verify Entry") < vbYes Then
Target.ClearContents
GoTo ws_exit
End If

Target.Resize(1, 3).Copy Target.Offset(0, 1)
Target.Clear


ws_exit:
Application.EnableEvents = True
End Sub

"Mark" wrote:

I entered Const WS_RANGE As String = "B1:B100, L1:L100"

When I write to L3 it copies to C4

I sorta want the L3 group to write to L4 as B3 writes to B4

Almost there.. What wonderful help..

Thank you.

Mark

"Toppers" wrote:

To execute another range:

change to:

Const WS_RANGE As String = "B1:B100, L1:L100"

Date must be ENTERED into L1:L100 as copy/paste doesn't invoke the code.

"Mark" wrote:

DANG! BERNIE!

If I could give you more than one Perfect rating I would Thank you, Thank
You, Thank You

Question. I have a duplicate of this on sheet 2:

Is there anyway to merger the 2 sheets. That is ,as if I would copy (sheet2)
From A1 to J100 and paste it into sheet 1 around L1? Can you run both on one
formula or must the entire thing be redone?

Thanks anyway fanfriggin tastic!

Mark

"Bernie Deitrick" wrote:

Sorry it didn't work : The code I have works acrros B3

It did not work in any cell between B4:b100

It does work, on every cell in the range B1:B100, as long as

1) you enter a value into a single cell
2) you enter a number

If you want to lift the single cell restriction, then remove


Or _
Target.Cells.Count 1

HTH,
Bernie
MS Excel MVP

Mark,

Give the version below a try.

HTH,
Bernie
MS Excel MVP


Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B4:B100, H4:H100"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Or _
IsEmpty(Target) Or _
Not IsNumeric(Target) Then
GoTo ws_exit
End If
If MsgBox("Use the new value " & Target & _
" as new Daily Entry?", vbYesNo + vbDefaultButton1 _
+ vbInformation, "Verify Entry") < vbYes Then
Target.ClearContents
GoTo ws_exit
End If

Target.Resize(1, 3).Copy Target.Offset(0, 1)
Target.Clear

If Target.Column = 8 Then
Cells(Target.Row, 15).Value = Cells(Target.Row, 14).Value
Cells(Target.Row, 14).Value = Cells(Target.Row, 13).Value
End If

ws_exit:
Application.EnableEvents = True
End Sub

On my sheet:

I enter a number in H (assuming I have 100 lines) it asks me to confirm and
when I say yes it moves to "I". The Old I moves to J, J to K and "L" is the
average of I,J,K.

M is now key! =(I4-L4)

As M changes. (with an entry into H)
I would like the old or M to move to N, N to O, and Old O drop off.

In M,N,O I am trying to track, the last 3 + 0r minus's. They shift right. As
does I,J,K when I put something in H.

Did I confuse the issue more. I'll stop. and try agin later.

mark, Thank you

That code works for b-L

Nothing on M,N or O

Here'a a deeper problem, one I was not smart enuff to see until now.

Example the code:
calculates for F column as C,D,E shifted.
It caculates a new L column as I,J,K shifted.

I wanted M to be plus or minus L.
The figure "THAT WAS" in L....
As I enter to make a new L
M is plus or minus the new L.
M needs to be plus or minus the # that was there, not the new number. It's a
score that must be matched. And if you do not score old L M needs to show how
much it was missed by.

It looks like now. I've been + or - the NEW L not the OLD L. Thats wrong.
You didn't know that, and neither did I till jsut now.

Further example.
the # in L was the score I had to make.
M needs to reflect if I made it or not.

When I enter a new # and L changes. M reflects the wrong # It reflects the
+/- of the score I need to make next not what I made against Old l.

Lets say my number was 20. in L ok?
I made16. M would be -4 right? ok Thats IS the problem!

Problem.. if I post the 16.. L changes,
lets say to 19 M now says -3
when it needs to" say -4" which was the last number I had to score.
Do you follow that. (I've messed this all up.).
M as I have been stupidly doing would calulate on the new L not the OLD L.

Mark

I give....
Bernie:
What on earth am I looking for?
M = + or - the number that was in L before I enter the new one.
I'm truly sorry. Maybe I need another column. One that retains the 2nd to
last L number. M calculates on that. Then as I enter numbers the 2nd to last
L is calculated by M.

Geez. Please feel free. to go to lunch and just drop me. I'm WAAAAAY over my
head and see the problems only when they pop up.




Mark,

Give the version below a try.

HTH,
Bernie
MS Excel MVP


Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B4:B100, H4:H100"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Or _
IsEmpty(Target) Or _
Not IsNumeric(Target) Then
GoTo ws_exit
End If
If MsgBox("Use the new value " & Target & _
" as new Daily Entry?", vbYesNo + vbDefaultButton1 _
+ vbInformation, "Verify Entry") < vbYes Then
Target.ClearContents
GoTo ws_exit
End If

Target.Resize(1, 3).Copy Target.Offset(0, 1)
Target.Clear

If Target.Column = 8 Then
Cells(Target.Row, 15).Value = Cells(Target.Row, 14).Value
Cells(Target.Row, 14).Value = Cells(Target.Row, 13).Value
End If

ws_exit:
Application.EnableEvents = True
End Sub

On my sheet:

I enter a number in H (assuming I have 100 lines) it asks me to confirm and
when I say yes it moves to "I". The Old I moves to J, J to K and "L" is the
average of I,J,K.

M is now key! =(I4-L4)

As M changes. (with an entry into H)
I would like the old or M to move to N, N to O, and Old O drop off.

In M,N,O I am trying to track, the last 3 + 0r minus's. They shift right. As
does I,J,K when I put something in H.

Did I confuse the issue more. I'll stop. and try agin later.

mark, Thank you

Mark,

Try this one:

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B4:B100, H4:H100"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Or _
IsEmpty(Target) Or _
Not IsNumeric(Target) Then
GoTo ws_exit
End If
If MsgBox("Use the new value " & Target & _
" as new Daily Entry?", vbYesNo + vbDefaultButton1 _
+ vbInformation, "Verify Entry") < vbYes Then
Target.ClearContents
GoTo ws_exit
End If

If Target.Column = 8 Then
Cells(Target.Row, 15).Value = Cells(Target.Row, 14).Value
Cells(Target.Row, 14).Value = Cells(Target.Row, 13).Value
End If

Target.Resize(1, 3).Copy Target.Offset(0, 1)
Target.Clear

ws_exit:
Application.EnableEvents = True
End Sub


HTH,
Bernie
MS Excel MVP


"Mark" wrote in message
...
That code works for b-L

Nothing on M,N or O

Here'a a deeper problem, one I was not smart enuff to see until now.

Example the code:
calculates for F column as C,D,E shifted.
It caculates a new L column as I,J,K shifted.

I wanted M to be plus or minus L.
The figure "THAT WAS" in L....
As I enter to make a new L
M is plus or minus the new L.
M needs to be plus or minus the # that was there, not the new number. It's a
score that must be matched. And if you do not score old L M needs to show how
much it was missed by.

It looks like now. I've been + or - the NEW L not the OLD L. Thats wrong.
You didn't know that, and neither did I till jsut now.

Further example.
the # in L was the score I had to make.
M needs to reflect if I made it or not.

When I enter a new # and L changes. M reflects the wrong # It reflects the
+/- of the score I need to make next not what I made against Old l.

Lets say my number was 20. in L ok?
I made16. M would be -4 right? ok Thats IS the problem!

Problem.. if I post the 16.. L changes,
lets say to 19 M now says -3
when it needs to" say -4" which was the last number I had to score.
Do you follow that. (I've messed this all up.).
M as I have been stupidly doing would calulate on the new L not the OLD L.

Mark

I give....
Bernie:
What on earth am I looking for?
M = + or - the number that was in L before I enter the new one.
I'm truly sorry. Maybe I need another column. One that retains the 2nd to
last L number. M calculates on that. Then as I enter numbers the 2nd to last
L is calculated by M.

Geez. Please feel free. to go to lunch and just drop me. I'm WAAAAAY over my
head and see the problems only when they pop up.




Mark,

Give the version below a try.

HTH,
Bernie
MS Excel MVP


Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B4:B100, H4:H100"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Or _
IsEmpty(Target) Or _
Not IsNumeric(Target) Then
GoTo ws_exit
End If
If MsgBox("Use the new value " & Target & _
" as new Daily Entry?", vbYesNo + vbDefaultButton1 _
+ vbInformation, "Verify Entry") < vbYes Then
Target.ClearContents
GoTo ws_exit
End If

Target.Resize(1, 3).Copy Target.Offset(0, 1)
Target.Clear

If Target.Column = 8 Then
Cells(Target.Row, 15).Value = Cells(Target.Row, 14).Value
Cells(Target.Row, 14).Value = Cells(Target.Row, 13).Value
End If

ws_exit:
Application.EnableEvents = True
End Sub

On my sheet:

I enter a number in H (assuming I have 100 lines) it asks me to confirm and
when I say yes it moves to "I". The Old I moves to J, J to K and "L" is the
average of I,J,K.

M is now key! =(I4-L4)

As M changes. (with an entry into H)
I would like the old or M to move to N, N to O, and Old O drop off.

In M,N,O I am trying to track, the last 3 + 0r minus's. They shift right. As
does I,J,K when I put something in H.

Did I confuse the issue more. I'll stop. and try agin later.

mark, Thank you

Bernie, Unbelievable.. Your code is perfect!

Thank you.

Now. of course ......there is always a now...isn't there?

L reads the score needed , lets say thats is 20
If I score 16 that would be -4. the -4 would history right from M,N,O.
However, if I enter 16, (L changes to average 19) and M reads -3 which is
incorrect. I scored 16 which is -4.

I love the +/- history moving in M,N,O But I guess M is sorta wrong because
M is reading L which has the new score in it.(16) -3.

Ideas?????

Mark




Obviously the question is If L was 20 and m should be -4 as soon as I enter
todays score M changes and reads todays difference -3
M reads the plus or minus. Histories shift.. Perfect.
If L reads 20 and I score 16 M should be -4.

However on entering 16 L changes to 19(average) and M reads -3 rather than
-4 which L was.

Any thoughts without loosing that fantastic shift history you have working?

"Bernie Deitrick" wrote:

Mark,

Try this one:

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B4:B100, H4:H100"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Or _
IsEmpty(Target) Or _
Not IsNumeric(Target) Then
GoTo ws_exit
End If
If MsgBox("Use the new value " & Target & _
" as new Daily Entry?", vbYesNo + vbDefaultButton1 _
+ vbInformation, "Verify Entry") < vbYes Then
Target.ClearContents
GoTo ws_exit
End If

If Target.Column = 8 Then
Cells(Target.Row, 15).Value = Cells(Target.Row, 14).Value
Cells(Target.Row, 14).Value = Cells(Target.Row, 13).Value
End If

Target.Resize(1, 3).Copy Target.Offset(0, 1)
Target.Clear

ws_exit:
Application.EnableEvents = True
End Sub


HTH,
Bernie
MS Excel MVP


"Mark" wrote in message
...
That code works for b-L

Nothing on M,N or O

Here'a a deeper problem, one I was not smart enuff to see until now.

Example the code:
calculates for F column as C,D,E shifted.
It caculates a new L column as I,J,K shifted.

I wanted M to be plus or minus L.
The figure "THAT WAS" in L....
As I enter to make a new L
M is plus or minus the new L.
M needs to be plus or minus the # that was there, not the new number. It's a
score that must be matched. And if you do not score old L M needs to show how
much it was missed by.

It looks like now. I've been + or - the NEW L not the OLD L. Thats wrong.
You didn't know that, and neither did I till jsut now.

Further example.
the # in L was the score I had to make.
M needs to reflect if I made it or not.

When I enter a new # and L changes. M reflects the wrong # It reflects the
+/- of the score I need to make next not what I made against Old l.

Lets say my number was 20. in L ok?
I made16. M would be -4 right? ok Thats IS the problem!

Problem.. if I post the 16.. L changes,
lets say to 19 M now says -3
when it needs to" say -4" which was the last number I had to score.
Do you follow that. (I've messed this all up.).
M as I have been stupidly doing would calulate on the new L not the OLD L.

Mark

I give....
Bernie:
What on earth am I looking for?
M = + or - the number that was in L before I enter the new one.
I'm truly sorry. Maybe I need another column. One that retains the 2nd to
last L number. M calculates on that. Then as I enter numbers the 2nd to last
L is calculated by M.

Geez. Please feel free. to go to lunch and just drop me. I'm WAAAAAY over my
head and see the problems only when they pop up.




Mark,

Give the version below a try.

HTH,
Bernie
MS Excel MVP


Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B4:B100, H4:H100"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Or _
IsEmpty(Target) Or _
Not IsNumeric(Target) Then
GoTo ws_exit
End If
If MsgBox("Use the new value " & Target & _
" as new Daily Entry?", vbYesNo + vbDefaultButton1 _
+ vbInformation, "Verify Entry") < vbYes Then
Target.ClearContents
GoTo ws_exit
End If

Target.Resize(1, 3).Copy Target.Offset(0, 1)
Target.Clear

If Target.Column = 8 Then
Cells(Target.Row, 15).Value = Cells(Target.Row, 14).Value
Cells(Target.Row, 14).Value = Cells(Target.Row, 13).Value
End If

ws_exit:
Application.EnableEvents = True
End Sub

On my sheet:

I enter a number in H (assuming I have 100 lines) it asks me to confirm and
when I say yes it moves to "I". The Old I moves to J, J to K and "L" is the
average of I,J,K.

M is now key! =(I4-L4)

As M changes. (with an entry into H)
I would like the old or M to move to N, N to O, and Old O drop off.

In M,N,O I am trying to track, the last 3 + 0r minus's. They shift right. As
does I,J,K when I put something in H.

Did I confuse the issue more. I'll stop. and try agin later.

mark, Thank you

Mark,

Certainly not perfect, but we'll get there....

Try this one, below.

HTH,
Bernie

MS Excel MVP

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B4:B100, H4:H100"
Dim myNewVal As Variant

On Error GoTo ws_exit:
Application.EnableEvents = False
If Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Or _
IsEmpty(Target) Or _
Not IsNumeric(Target) Then
GoTo ws_exit
End If
If MsgBox("Use the new value " & Target & _
" as new Daily Entry?", vbYesNo + vbDefaultButton1 _
+ vbInformation, "Verify Entry") < vbYes Then
Target.ClearContents
GoTo ws_exit
End If

myNewVal = Target.Value
With Application
.Undo
.CalculateFull
End With
If Target.Column = 8 Then
Cells(Target.Row, 15).Value = Cells(Target.Row, 14).Value
Cells(Target.Row, 14).Value = Cells(Target.Row, 13).Value
End If
Target.Value = myNewVal
Target.Resize(1, 3).Copy Target.Offset(0, 1)
Target.Clear

ws_exit:
Application.EnableEvents = True
End Sub
"Mark" wrote in message
...
Bernie, Unbelievable.. Your code is perfect!

Thank you.

Now. of course ......there is always a now...isn't there?

L reads the score needed , lets say thats is 20
If I score 16 that would be -4. the -4 would history right from M,N,O.
However, if I enter 16, (L changes to average 19) and M reads -3 which is
incorrect. I scored 16 which is -4.

I love the +/- history moving in M,N,O But I guess M is sorta wrong
because
M is reading L which has the new score in it.(16) -3.

Ideas?????

Mark




Obviously the question is If L was 20 and m should be -4 as soon as I
enter
todays score M changes and reads todays difference -3
M reads the plus or minus. Histories shift.. Perfect.
If L reads 20 and I score 16 M should be -4.

However on entering 16 L changes to 19(average) and M reads -3 rather than
-4 which L was.

Any thoughts without loosing that fantastic shift history you have
working?

"Bernie Deitrick" wrote:

Mark,

Try this one:

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B4:B100, H4:H100"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Or _
IsEmpty(Target) Or _
Not IsNumeric(Target) Then
GoTo ws_exit
End If
If MsgBox("Use the new value " & Target & _
" as new Daily Entry?", vbYesNo + vbDefaultButton1 _
+ vbInformation, "Verify Entry") < vbYes Then
Target.ClearContents
GoTo ws_exit
End If

If Target.Column = 8 Then
Cells(Target.Row, 15).Value = Cells(Target.Row, 14).Value
Cells(Target.Row, 14).Value = Cells(Target.Row, 13).Value
End If

Target.Resize(1, 3).Copy Target.Offset(0, 1)
Target.Clear

ws_exit:
Application.EnableEvents = True
End Sub


HTH,
Bernie
MS Excel MVP


"Mark" wrote in message
...
That code works for b-L

Nothing on M,N or O

Here'a a deeper problem, one I was not smart enuff to see until now.

Example the code:
calculates for F column as C,D,E shifted.
It caculates a new L column as I,J,K shifted.

I wanted M to be plus or minus L.
The figure "THAT WAS" in L....
As I enter to make a new L
M is plus or minus the new L.
M needs to be plus or minus the # that was there, not the new number.
It's a
score that must be matched. And if you do not score old L M needs to
show how
much it was missed by.

It looks like now. I've been + or - the NEW L not the OLD L. Thats
wrong.
You didn't know that, and neither did I till jsut now.

Further example.
the # in L was the score I had to make.
M needs to reflect if I made it or not.

When I enter a new # and L changes. M reflects the wrong # It reflects
the
+/- of the score I need to make next not what I made against Old l.

Lets say my number was 20. in L ok?
I made16. M would be -4 right? ok Thats IS the problem!

Problem.. if I post the 16.. L changes,
lets say to 19 M now says -3
when it needs to" say -4" which was the last number I had to score.
Do you follow that. (I've messed this all up.).
M as I have been stupidly doing would calulate on the new L not the OLD
L.

Mark

I give....
Bernie:
What on earth am I looking for?
M = + or - the number that was in L before I enter the new one.
I'm truly sorry. Maybe I need another column. One that retains the 2nd
to
last L number. M calculates on that. Then as I enter numbers the 2nd to
last
L is calculated by M.

Geez. Please feel free. to go to lunch and just drop me. I'm WAAAAAY
over my
head and see the problems only when they pop up.




Mark,

Give the version below a try.

HTH,
Bernie
MS Excel MVP


Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B4:B100, H4:H100"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Or _
IsEmpty(Target) Or _
Not IsNumeric(Target) Then
GoTo ws_exit
End If
If MsgBox("Use the new value " & Target & _
" as new Daily Entry?", vbYesNo + vbDefaultButton1 _
+ vbInformation, "Verify Entry") < vbYes Then
Target.ClearContents
GoTo ws_exit
End If

Target.Resize(1, 3).Copy Target.Offset(0, 1)
Target.Clear

If Target.Column = 8 Then
Cells(Target.Row, 15).Value = Cells(Target.Row, 14).Value
Cells(Target.Row, 14).Value = Cells(Target.Row, 13).Value
End If

ws_exit:
Application.EnableEvents = True
End Sub

On my sheet:

I enter a number in H (assuming I have 100 lines) it asks me to
confirm and
when I say yes it moves to "I". The Old I moves to J, J to K and "L"
is the
average of I,J,K.

M is now key! =(I4-L4)

As M changes. (with an entry into H)
I would like the old or M to move to N, N to O, and Old O drop off.

In M,N,O I am trying to track, the last 3 + 0r minus's. They shift
right. As
does I,J,K when I put something in H.

Did I confuse the issue more. I'll stop. and try agin later.

mark, Thank you

Bernie.. I HAVE IT.. THEE QUESTION.

In this code:
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B4:B100, H4:H100"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Or _
IsEmpty(Target) Or _
Not IsNumeric(Target) Then
GoTo ws_exit
End If
If MsgBox("Use the new value " & Target & _
" as new Daily Entry?", vbYesNo + vbDefaultButton1 _
+ vbInformation, "Verify Entry") < vbYes Then
Target.ClearContents
GoTo ws_exit
End If

If Target.Column = 8 Then
Cells(Target.Row, 15).Value = Cells(Target.Row, 14).Value
Cells(Target.Row, 14).Value = Cells(Target.Row, 13).Value
End If

Target.Resize(1, 3).Copy Target.Offset(0, 1)
Target.Clear

ws_exit:
Application.EnableEvents = True
End Sub

Is it possible to create a hot key..
That inserts a column left of M, copy L4:L100 and paste special "VALUE" to
M4:M100.

Answer =No.. I insert one.
I copy L4:L100 paste special VALUE to M4:M100

If yes" ok, then
Move M,N,O to N,O,P

N4:N100 is now =I4-M4

If NO:
I can't see how code could get us that "paste special VALUE to M. So I quit
and will do the rest by hand..

Have a good night sir..

Mark




"Bernie Deitrick" wrote:

Mark,

Certainly not perfect, but we'll get there....

Try this one, below.

HTH,
Bernie

MS Excel MVP

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B4:B100, H4:H100"
Dim myNewVal As Variant

On Error GoTo ws_exit:
Application.EnableEvents = False
If Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Or _
IsEmpty(Target) Or _
Not IsNumeric(Target) Then
GoTo ws_exit
End If
If MsgBox("Use the new value " & Target & _
" as new Daily Entry?", vbYesNo + vbDefaultButton1 _
+ vbInformation, "Verify Entry") < vbYes Then
Target.ClearContents
GoTo ws_exit
End If

myNewVal = Target.Value
With Application
.Undo
.CalculateFull
End With
If Target.Column = 8 Then
Cells(Target.Row, 15).Value = Cells(Target.Row, 14).Value
Cells(Target.Row, 14).Value = Cells(Target.Row, 13).Value
End If
Target.Value = myNewVal
Target.Resize(1, 3).Copy Target.Offset(0, 1)
Target.Clear

ws_exit:
Application.EnableEvents = True
End Sub
"Mark" wrote in message
...
Bernie, Unbelievable.. Your code is perfect!

Thank you.

Now. of course ......there is always a now...isn't there?

L reads the score needed , lets say thats is 20
If I score 16 that would be -4. the -4 would history right from M,N,O.
However, if I enter 16, (L changes to average 19) and M reads -3 which is
incorrect. I scored 16 which is -4.

I love the +/- history moving in M,N,O But I guess M is sorta wrong
because
M is reading L which has the new score in it.(16) -3.

Ideas?????

Mark




Obviously the question is If L was 20 and m should be -4 as soon as I
enter
todays score M changes and reads todays difference -3
M reads the plus or minus. Histories shift.. Perfect.
If L reads 20 and I score 16 M should be -4.

However on entering 16 L changes to 19(average) and M reads -3 rather than
-4 which L was.

Any thoughts without loosing that fantastic shift history you have
working?

"Bernie Deitrick" wrote:

Mark,

Try this one:

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B4:B100, H4:H100"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Or _
IsEmpty(Target) Or _
Not IsNumeric(Target) Then
GoTo ws_exit
End If
If MsgBox("Use the new value " & Target & _
" as new Daily Entry?", vbYesNo + vbDefaultButton1 _
+ vbInformation, "Verify Entry") < vbYes Then
Target.ClearContents
GoTo ws_exit
End If

If Target.Column = 8 Then
Cells(Target.Row, 15).Value = Cells(Target.Row, 14).Value
Cells(Target.Row, 14).Value = Cells(Target.Row, 13).Value
End If

Target.Resize(1, 3).Copy Target.Offset(0, 1)
Target.Clear

ws_exit:
Application.EnableEvents = True
End Sub


HTH,
Bernie
MS Excel MVP


"Mark" wrote in message
...
That code works for b-L

Nothing on M,N or O

Here'a a deeper problem, one I was not smart enuff to see until now.

Example the code:
calculates for F column as C,D,E shifted.
It caculates a new L column as I,J,K shifted.

I wanted M to be plus or minus L.
The figure "THAT WAS" in L....
As I enter to make a new L
M is plus or minus the new L.
M needs to be plus or minus the # that was there, not the new number.
It's a
score that must be matched. And if you do not score old L M needs to
show how
much it was missed by.

It looks like now. I've been + or - the NEW L not the OLD L. Thats
wrong.
You didn't know that, and neither did I till jsut now.

Further example.
the # in L was the score I had to make.
M needs to reflect if I made it or not.

When I enter a new # and L changes. M reflects the wrong # It reflects
the
+/- of the score I need to make next not what I made against Old l.

Lets say my number was 20. in L ok?
I made16. M would be -4 right? ok Thats IS the problem!

Problem.. if I post the 16.. L changes,
lets say to 19 M now says -3
when it needs to" say -4" which was the last number I had to score.
Do you follow that. (I've messed this all up.).
M as I have been stupidly doing would calulate on the new L not the OLD
L.

Mark

I give....
Bernie:
What on earth am I looking for?
M = + or - the number that was in L before I enter the new one.
I'm truly sorry. Maybe I need another column. One that retains the 2nd
to
last L number. M calculates on that. Then as I enter numbers the 2nd to
last
L is calculated by M.

Geez. Please feel free. to go to lunch and just drop me. I'm WAAAAAY
over my
head and see the problems only when they pop up.




Mark,

Give the version below a try.

HTH,
Bernie
MS Excel MVP


Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B4:B100, H4:H100"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Or _
IsEmpty(Target) Or _
Not IsNumeric(Target) Then
GoTo ws_exit
End If
If MsgBox("Use the new value " & Target & _
" as new Daily Entry?", vbYesNo + vbDefaultButton1 _
+ vbInformation, "Verify Entry") < vbYes Then
Target.ClearContents
GoTo ws_exit
End If

Target.Resize(1, 3).Copy Target.Offset(0, 1)
Target.Clear

If Target.Column = 8 Then
Cells(Target.Row, 15).Value = Cells(Target.Row, 14).Value
Cells(Target.Row, 14).Value = Cells(Target.Row, 13).Value
End If

ws_exit:
Application.EnableEvents = True
End Sub

On my sheet:

I enter a number in H (assuming I have 100 lines) it asks me to
confirm and
when I say yes it moves to "I". The Old I moves to J, J to K and "L"
is the
average of I,J,K.

M is now key! =(I4-L4)

As M changes. (with an entry into H)
I would like the old or M to move to N, N to O, and Old O drop off.

In M,N,O I am trying to track, the last 3 + 0r minus's. They shift
right. As
does I,J,K when I put something in H.

Did I confuse the issue more. I'll stop. and try agin later.

mark, Thank you

To create a 'hot key' with an event is difficult, but you could use a 'hot value' that you enter in
the cell to trigger some other code:

Replace this:

If Target.Column = 8 Then
Cells(Target.Row, 15).Value = Cells(Target.Row, 14).Value
Cells(Target.Row, 14).Value = Cells(Target.Row, 13).Value
End If

with this


If Target.Column = 8 Then
If Target.Value = "M" Then
Range("M:M").Insert
Range("L:L").Copy
Range("M:M").PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
Else
Cells(Target.Row, 15).Value = Cells(Target.Row, 14).Value
Cells(Target.Row, 14).Value = Cells(Target.Row, 13).Value
End If
End If

Then enter M into a cell in column H to create the copy of column L....

HTH,
Bernie
MS Excel MVP


"Mark" wrote in message
...

Bernie.. I HAVE IT.. THEE QUESTION.

In this code:
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B4:B100, H4:H100"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Or _
IsEmpty(Target) Or _
Not IsNumeric(Target) Then
GoTo ws_exit
End If
If MsgBox("Use the new value " & Target & _
" as new Daily Entry?", vbYesNo + vbDefaultButton1 _
+ vbInformation, "Verify Entry") < vbYes Then
Target.ClearContents
GoTo ws_exit
End If

If Target.Column = 8 Then
Cells(Target.Row, 15).Value = Cells(Target.Row, 14).Value
Cells(Target.Row, 14).Value = Cells(Target.Row, 13).Value
End If

Target.Resize(1, 3).Copy Target.Offset(0, 1)
Target.Clear

ws_exit:
Application.EnableEvents = True
End Sub

Is it possible to create a hot key..
That inserts a column left of M, copy L4:L100 and paste special "VALUE" to
M4:M100.

Answer =No.. I insert one.
I copy L4:L100 paste special VALUE to M4:M100

If yes" ok, then
Move M,N,O to N,O,P

N4:N100 is now =I4-M4

If NO:
I can't see how code could get us that "paste special VALUE to M. So I quit
and will do the rest by hand..

Have a good night sir..

Mark




"Bernie Deitrick" wrote:

Mark,

Certainly not perfect, but we'll get there....

Try this one, below.

HTH,
Bernie

MS Excel MVP

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B4:B100, H4:H100"
Dim myNewVal As Variant

On Error GoTo ws_exit:
Application.EnableEvents = False
If Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Or _
IsEmpty(Target) Or _
Not IsNumeric(Target) Then
GoTo ws_exit
End If
If MsgBox("Use the new value " & Target & _
" as new Daily Entry?", vbYesNo + vbDefaultButton1 _
+ vbInformation, "Verify Entry") < vbYes Then
Target.ClearContents
GoTo ws_exit
End If

myNewVal = Target.Value
With Application
.Undo
.CalculateFull
End With
If Target.Column = 8 Then
Cells(Target.Row, 15).Value = Cells(Target.Row, 14).Value
Cells(Target.Row, 14).Value = Cells(Target.Row, 13).Value
End If
Target.Value = myNewVal
Target.Resize(1, 3).Copy Target.Offset(0, 1)
Target.Clear

ws_exit:
Application.EnableEvents = True
End Sub
"Mark" wrote in message
...
Bernie, Unbelievable.. Your code is perfect!

Thank you.

Now. of course ......there is always a now...isn't there?

L reads the score needed , lets say thats is 20
If I score 16 that would be -4. the -4 would history right from M,N,O.
However, if I enter 16, (L changes to average 19) and M reads -3 which is
incorrect. I scored 16 which is -4.

I love the +/- history moving in M,N,O But I guess M is sorta wrong
because
M is reading L which has the new score in it.(16) -3.

Ideas?????

Mark




Obviously the question is If L was 20 and m should be -4 as soon as I
enter
todays score M changes and reads todays difference -3
M reads the plus or minus. Histories shift.. Perfect.
If L reads 20 and I score 16 M should be -4.

However on entering 16 L changes to 19(average) and M reads -3 rather than
-4 which L was.

Any thoughts without loosing that fantastic shift history you have
working?

"Bernie Deitrick" wrote:

Mark,

Try this one:

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B4:B100, H4:H100"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Or _
IsEmpty(Target) Or _
Not IsNumeric(Target) Then
GoTo ws_exit
End If
If MsgBox("Use the new value " & Target & _
" as new Daily Entry?", vbYesNo + vbDefaultButton1 _
+ vbInformation, "Verify Entry") < vbYes Then
Target.ClearContents
GoTo ws_exit
End If

If Target.Column = 8 Then
Cells(Target.Row, 15).Value = Cells(Target.Row, 14).Value
Cells(Target.Row, 14).Value = Cells(Target.Row, 13).Value
End If

Target.Resize(1, 3).Copy Target.Offset(0, 1)
Target.Clear

ws_exit:
Application.EnableEvents = True
End Sub


HTH,
Bernie
MS Excel MVP


"Mark" wrote in message
...
That code works for b-L

Nothing on M,N or O

Here'a a deeper problem, one I was not smart enuff to see until now.

Example the code:
calculates for F column as C,D,E shifted.
It caculates a new L column as I,J,K shifted.

I wanted M to be plus or minus L.
The figure "THAT WAS" in L....
As I enter to make a new L
M is plus or minus the new L.
M needs to be plus or minus the # that was there, not the new number.
It's a
score that must be matched. And if you do not score old L M needs to
show how
much it was missed by.

It looks like now. I've been + or - the NEW L not the OLD L. Thats
wrong.
You didn't know that, and neither did I till jsut now.

Further example.
the # in L was the score I had to make.
M needs to reflect if I made it or not.

When I enter a new # and L changes. M reflects the wrong # It reflects
the
+/- of the score I need to make next not what I made against Old l.

Lets say my number was 20. in L ok?
I made16. M would be -4 right? ok Thats IS the problem!

Problem.. if I post the 16.. L changes,
lets say to 19 M now says -3
when it needs to" say -4" which was the last number I had to score.
Do you follow that. (I've messed this all up.).
M as I have been stupidly doing would calulate on the new L not the OLD
L.

Mark

I give....
Bernie:
What on earth am I looking for?
M = + or - the number that was in L before I enter the new one.
I'm truly sorry. Maybe I need another column. One that retains the 2nd
to
last L number. M calculates on that. Then as I enter numbers the 2nd to
last
L is calculated by M.

Geez. Please feel free. to go to lunch and just drop me. I'm WAAAAAY
over my
head and see the problems only when they pop up.




Mark,

Give the version below a try.

HTH,
Bernie
MS Excel MVP


Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B4:B100, H4:H100"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Or _
IsEmpty(Target) Or _
Not IsNumeric(Target) Then
GoTo ws_exit
End If
If MsgBox("Use the new value " & Target & _
" as new Daily Entry?", vbYesNo + vbDefaultButton1 _
+ vbInformation, "Verify Entry") < vbYes Then
Target.ClearContents
GoTo ws_exit
End If

Target.Resize(1, 3).Copy Target.Offset(0, 1)
Target.Clear

If Target.Column = 8 Then
Cells(Target.Row, 15).Value = Cells(Target.Row, 14).Value
Cells(Target.Row, 14).Value = Cells(Target.Row, 13).Value
End If

ws_exit:
Application.EnableEvents = True
End Sub

On my sheet:

I enter a number in H (assuming I have 100 lines) it asks me to
confirm and
when I say yes it moves to "I". The Old I moves to J, J to K and "L"
is the
average of I,J,K.

M is now key! =(I4-L4)

As M changes. (with an entry into H)
I would like the old or M to move to N, N to O, and Old O drop off.

In M,N,O I am trying to track, the last 3 + 0r minus's. They shift
right. As
does I,J,K when I put something in H.

Did I confuse the issue more. I'll stop. and try agin later.

mark, Thank you