Get value from cell when another cell changes then reset

[flatpro]

When cell A1 changes in Excel to 1 (fed via an external com process) I want another cell, A3, to display the value of cell A2 (also fed by an external program)


I need A3 to keep displaying the same value of A1 until A4 displays 'A' at which time A3 to start to monitor for changes to A1 again (A1 will now not display 0)


Does anyone know if this is possible in vba...?

 

[JLGWhiz]

When cell A1 changes in Excel to 1 (fed via an external com process) I want another cell, A3, to display the value of cell A2 (also fed by an external program)


I need A3 to keep displaying the same value of A1 until A4 displays 'A' at which time A3 to start to monitor for changes to A1 again (A1 will now not display 0)


Does anyone know if this is possible in vba...?

Paragraph 2 conflicts with the requiremnt of paragraph 1. What does A3 display and what is the criteria for A3 to switch from monitoring A1 to A2 and vice versa?

 

[flatpro]

Sorry!

I meant,

"When cell A1 changes in Excel to 1 (fed via an external com process) I want another cell, A3, to display the value of cell A2 (also fed by an external program)


I need A3 to keep displaying the same value of A2 until A4 displays 'A' at which time A3 to start to monitor A1 for changes again (A1 will not display 1)"

So if A1 = 1
A3 = A2

Until A4 = 'A'
(At this point A1 will != 1)
Start monitoring the value of A1 again, like a reset...

 

[JLGWhiz]

I'll take a stab at it. Copy this code to the code module in the worksheet. To access the code module, right click the sheet name tab, then click 'View Code' in the pop up menu. If the the data in A1:A4 does not perform as desired, then delete the code from the sheet code module, to prevent the automatic responses.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = $A$1 And Target = 1 And Range("A4") <> "A" Then
 Range("A3") = Range("A2").Value
End If
End Sub