I want to manipulate the value of a cell following a Worksheet_Change event in a private subroutine. I call another subroutine to do the actual change but every time I change the value of a cell,
VB kicks me out of my subroutine back to the calling private subroutine. I am not done with processing in my subroutine. I've tried several ways to access the cell without getting kicked back. What can I do to continue processing after changing the value?
(I left my various tries commented out below)
Private Sub Worksheet_Change(ByVal Target As Range)
If IsEmpty(Target) Then
Exit Sub
End If
'set target cell to J9000 on All Prices tab, for value change
If Target.Address = "All Prices!$J$9000" Then
'check to see if cell is numeric
If IsNumeric(Target) Then
Call OPIS_Change
End If
End If
End Sub
Sub OPIS_Change()
If Cells(5, "AA").Value = 2 Then
Exit Sub
Else
If Cells(5, "AA").Value <> 1 Then
Exit Sub
End If
End If
'move value of AA4 to AA6
'Cells(6, AA) = Cells(4, AA)
Range("AA6").Value = Range("AA4").Value
'Cells(6, "AA").Value = Cells(4, "AA").Value
'CODE ALWAYS JUMPS BACK TO VERY TOP AFTER MOVE
'*^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
'Mail a copy of the ActiveWorkbook with another file name
Dim wb1 As Workbook
Dim TempFilePath As String
... (etc)