On Saturday, January 18, 2014 1:37:46 AM UTC-8, Claus Busch wrote:
Hi Howard,



Am Sat, 18 Jan 2014 01:22:09 -0800 (PST) schrieb L. Howard:



The problem is with the Case 61, 62.



The values in col 61 and 62 are results of stock prices downloaded from some external source of Excel.



try:



Private Sub Worksheet_Calculate()

Dim LRow As Long

Dim rngC As Range



LRow = Cells(Rows.Count, 61).End(xlUp).Row

For Each rngC In Range("BI1:BI" & LRow)

If rngC.Value * rngC.Offset(, 1).Value < 0 Then

Cells(rngC.Row, 46) = Cells(rngC.Row, 49)

End If

Next

End Sub



Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("AW:AW")) Is Nothing _

Or Target.Count 1 Then Exit Sub



Dim rngAW As Range

Dim LRow As Long

Dim c As Range



LRow = Cells(Rows.Count, 49).End(xlUp).Row

Set rngAW = Range("AW6:AW" & LRow)



For Each c In rngAW

If c.Offset(, -3) = "" Then

If c 0 Then

c.Offset(, -3) = c

End If

End If

Next



End Sub





Regards

Claus B.


Thanks Claus.

I am assuming it will take a real life stock price up date to properly test the code.

If I do any manual entries it makes the proper copies to column 46 from column 49 but then goes into a continuous loop.

Probably won't know until Monday. (Markets closed)

I trust the code much more than the incredibly complex worksheet I'm using in on.

Howard