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