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