Request macro code - when cell change event
 

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

--
Rhey197
-----------------------------------------------------------------------
Rhey1971's Profile: http://www.excelforum.com/member.php...fo&userid=1233
View this thread: http://www.excelforum.com/showthread.php?threadid=53768

Request macro code - when cell change event
 

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" wrote in
message ...

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

Request macro code - when cell change event
 

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" a écrit
dans le message de
...

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

Request macro code - when cell change event
 

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


--
mudraker
------------------------------------------------------------------------
mudraker's Profile: http://www.excelforum.com/member.php...fo&userid=2473
View this thread: http://www.excelforum.com/showthread...hreadid=537685

Request macro code - when cell change event
 

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" a écrit dans le message de
...
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" a écrit
dans le message de
...

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