Request macro code - when cell change event

[Rhey1971]

I wish to request a macro code on this:
1. I have 3 cells A1 A2 and A3
2. Let's say that A1=5, A2=6, and A3=7
3. Here are the scenarios:
a. When I change A1 from 5 to 10, A2 becomes, 11 and A3
becomes 12, meaning, that when A1 incremented by a value of
5, A2 and A3 automatically increments by 5 each.
b. When I further chage A1, this time, from 10 to 14, then A2
becomes 15, and A3 becomes 16.
c. But if I reset the value of A1 to zero or decrease its value
to any number lower than its most previous value, A2 and A3
would not change. This means that A2 and A3 can only
accumulate but not decrease when A1's value is decreased.
Therefore, changing A1 from 14 to 10, A2 and A3 remains 15
and 16.

Thank you for any assistance.

[Bob Phillips]

Option Explicit

Const WS_RANGE As String = "A1"
Private myCell

'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
If Target.Value > myCell Then
Target.Offset(1, 0).Value = Target.Value + 1
Target.Offset(2, 0).Value = Target.Value + 2
End If
End If

ws_exit:
Application.EnableEvents = True
End Sub

'-----------------------------------------------------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'-----------------------------------------------------------------

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
myCell = Target.Value
End If
End Sub


'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.




--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Rhey1971" <[email protected]> wrote in
message news:[email protected]...
>
> I wish to request a macro code on this:
> 1. I have 3 cells A1 A2 and A3
> 2. Let's say that A1=5, A2=6, and A3=7
> 3. Here are the scenarios:
> a. When I change A1 from 5 to 10, A2 becomes, 11 and A3
> becomes 12, meaning, that when A1 incremented by a value of
> 5, A2 and A3 automatically increments by 5 each.
> b. When I further chage A1, this time, from 10 to 14, then A2
> becomes 15, and A3 becomes 16.
> c. But if I reset the value of A1 to zero or decrease its value
> to any number lower than its most previous value, A2 and A3
> would not change. This means that A2 and A3 can only
> accumulate but not decrease when A1's value is decreased.
> Therefore, changing A1 from 14 to 10, A2 and A3 remains 15
> and 16.
>
> Thank you for any assistance.
>
>
> --
> Rhey1971
> ------------------------------------------------------------------------
> Rhey1971's Profile:
http://www.excelforum.com/member.php...o&userid=12337
> View this thread: http://www.excelforum.com/showthread...hreadid=537685
>

[mudraker]

You need several change event macros as you need to be able to track the value of a1 from the time the workbook is opend or the sheet is activated

try these 3

copy and paste all on to the module for your sheet

Option Explicit

Dim iA1 As Integer

Private Sub Worksheet_Activate()
If Target.Address = "$A$1" Then
iA1 = Range("a1").Value
End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Dim iDif As Integer
If Target.Address = "$A$1" Then
If iA1 < Target.Value Then
Application.EnableEvents = False
iDif = Target.Value - iA1
Range("a2").Value = Range("a2").Value + iDif
Range("a3").Value = Range("a3").Value + iDif
End If
iA1 = Target.Value
End If
Application.EnableEvents = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$A$1" Then
iA1 = Range("a1").Value
End If
End Sub

[Ardus Petus]

Paste the following in Worksheet code:

'----------
Private Sub Worksheet_Change(ByVal Target As Range)
Static prevValue As Long
Dim iDiff As Long
If Intersect(Target, Range("A1")) Is Nothing _
Or Target.Count > 1 Then Exit Sub
If Target.Value > prevValue Then
iDiff = Target.Value - prevValue
Range("A2").Value = Range("A2").Value + iDiff
Range("A3").Value = Range("A3").Value + iDiff
End If
prevValue = Target.Value
End Sub
'----------

TH
--
AP

"Rhey1971" <[email protected]> a écrit
dans le message de
news:[email protected]...
>
> I wish to request a macro code on this:
> 1. I have 3 cells A1 A2 and A3
> 2. Let's say that A1=5, A2=6, and A3=7
> 3. Here are the scenarios:
> a. When I change A1 from 5 to 10, A2 becomes, 11 and A3
> becomes 12, meaning, that when A1 incremented by a value of
> 5, A2 and A3 automatically increments by 5 each.
> b. When I further chage A1, this time, from 10 to 14, then A2
> becomes 15, and A3 becomes 16.
> c. But if I reset the value of A1 to zero or decrease its value
> to any number lower than its most previous value, A2 and A3
> would not change. This means that A2 and A3 can only
> accumulate but not decrease when A1's value is decreased.
> Therefore, changing A1 from 14 to 10, A2 and A3 remains 15
> and 16.
>
> Thank you for any assistance.
>
>
> --
> Rhey1971
> ------------------------------------------------------------------------
> Rhey1971's Profile:
http://www.excelforum.com/member.php...o&userid=12337
> View this thread: http://www.excelforum.com/showthread...hreadid=537685
>

[Ardus Petus]

Oooops!

Correct code:

'--------
Private Sub Worksheet_Change(ByVal Target As Range)
Static prevValue As Long
Dim iDiff As Long
If Intersect(Target, Range("A1")) Is Nothing _
Or Target.Count > 1 Then Exit Sub
If Target.Value > prevValue Then
iDiff = Target.Value - prevValue
Application.EnableEvents = False
Range("A2").Value = Range("A2").Value + iDiff
Range("A3").Value = Range("A3").Value + iDiff
Application.EnableEvents = True
End If
prevValue = Target.Value
End Sub
'--------

"Ardus Petus" <[email protected]> a écrit dans le message de
news:[email protected]...
> Paste the following in Worksheet code:
>
> '----------
> Private Sub Worksheet_Change(ByVal Target As Range)
> Static prevValue As Long
> Dim iDiff As Long
> If Intersect(Target, Range("A1")) Is Nothing _
> Or Target.Count > 1 Then Exit Sub
> If Target.Value > prevValue Then
> iDiff = Target.Value - prevValue
> Range("A2").Value = Range("A2").Value + iDiff
> Range("A3").Value = Range("A3").Value + iDiff
> End If
> prevValue = Target.Value
> End Sub
> '----------
>
> TH
> --
> AP
>
> "Rhey1971" <[email protected]> a écrit
> dans le message de
> news:[email protected]...
> >
> > I wish to request a macro code on this:
> > 1. I have 3 cells A1 A2 and A3
> > 2. Let's say that A1=5, A2=6, and A3=7
> > 3. Here are the scenarios:
> > a. When I change A1 from 5 to 10, A2 becomes, 11 and A3
> > becomes 12, meaning, that when A1 incremented by a value of
> > 5, A2 and A3 automatically increments by 5 each.
> > b. When I further chage A1, this time, from 10 to 14, then A2
> > becomes 15, and A3 becomes 16.
> > c. But if I reset the value of A1 to zero or decrease its value
> > to any number lower than its most previous value, A2 and A3
> > would not change. This means that A2 and A3 can only
> > accumulate but not decrease when A1's value is decreased.
> > Therefore, changing A1 from 14 to 10, A2 and A3 remains 15
> > and 16.
> >
> > Thank you for any assistance.
> >
> >
> > --
> > Rhey1971
> > ------------------------------------------------------------------------
> > Rhey1971's Profile:
> http://www.excelforum.com/member.php...o&userid=12337
> > View this thread:
http://www.excelforum.com/showthread...hreadid=537685
> >
>
>