Condition Check for the Current Cell

treeppm

Board Regular
Joined
Jun 3, 2015
Messages
60
Hello Friends,
Cell A1=1, A2=1, A3=A1*A2

When I change A1 Value and if it gives A3>previous value I would like to store the A1=A4
kindly guide me

Regards
 
Last edited:
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
 
Upvote 0

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
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

No, It's not working bro, Kindly recheck
 
Upvote 0
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.

Do cells A1 and A2 always get changed at the same time?
If so, I think a different approach is required.
The present code acts on changes to A1 and A2 individually, so that might produce results you do not want.
 
Upvote 0
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
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.
 
Last edited:
Upvote 0
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?
 
Upvote 0
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?
 
Upvote 0
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?

Exactly, and you can add as much as cells you want for the calculation or storing values.
This is the requirement.
We are having value a,b for the multiply
so the multiply goes to x
if we change a or b and if the current value of x is higher than previous x then we need only current higher x of "a" value
 
Last edited:
Upvote 0
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
 
Upvote 0
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
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..
Have great life bro,
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,324
Members
452,635
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top