Private Sub Worksheet_Change(ByVal Target As Range)
Dim x#, y#, z#
If Target.Address <> "$A$1" And Target.Address <> "$A$2" Then Exit Sub
Application.EnableEvents = False
x = [A1]
z = [A2]
y = [A3]
Application.Undo
[COLOR=#ff0000]If [A3] > y Then [A4] = x[/COLOR]
[A1] = x
[A2] = z
Application.EnableEvents = True
End Sub
Code:Private Sub Worksheet_Change(ByVal Target As Range) Dim x#, y#, z# If Target.Address <> "$A$1" And Target.Address <> "$A$2" Then Exit Sub Application.EnableEvents = False x = [A1] z = [A2] y = [A3] Application.Undo [COLOR=#ff0000]If [A3] > y Then [A4] = x[/COLOR] [A1] = x [A2] = z Application.EnableEvents = True End Sub
Do you mean : if at any time in the past the value in A3 was higher than the current A3 value then don't change A4 ?
If so, I think another cell will be needed to store the highest A3 value.
There is another choice bro, you can store the higher A3 value into cell B3 so when ever it goes greater than the current A3 value you can replace B3 with current A3 value and you can store the current A1 value into A4
PS: I think you mentioning this in
What about my other questions?
Yes, But I need only the A1 value if A1*A2 multiply goes higher than previousDo cells A1 and A2 always get changed at the same time?
Yes, But I need only the A1 value if A1*A2 multiply goes higher than previous
Yes but if A1 is changed the A3 value changes and the code gets triggered - and uses that A3 value to compare.
Then if A2 is changed the A3 value changes and the code gets triggered - and then uses that new A3 value to compare.
Is that what you want?
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$1" And Target.Address <> "$A$2" Then Exit Sub
Application.EnableEvents = False
If [A3] > [B3] Then
[A4] = [A1]
[B3] = [A3]
End If
Application.EnableEvents = True
End Sub
Brilliant work dude, this is exactly what I want. Thank you so much for the great help with great patient, even I could not explain in perfect English..Code:Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address <> "$A$1" And Target.Address <> "$A$2" Then Exit Sub Application.EnableEvents = False If [A3] > [B3] Then [A4] = [A1] [B3] = [A3] End If Application.EnableEvents = True End Sub