look at multiple cells in a column and change a value
 

How can i look at multiple cells in a column and if any of the values change
then add them or take them away from a another cell in a different column

Thankyou

look at multiple cells in a column and change a value
 

You could use an event macro.

Assume the range of interest is A1:A100 (adjust to suit) and cell to change
is C10.

Amend logic to determine add /subtraction

Select the sheet where you want this to happen.
Right click the sheet tab and select View Code.
Paste this code into the window that opens.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo ErrHandler
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:A100")) Is Nothing Then
Range("C10")= Range("C10")+Target.value
End If
ErrHandler:
Application.EnableEvents = True
End Sub

Hit ALT Q to return to Excel.

HTH

"RobG2007" wrote:

How can i look at multiple cells in a column and if any of the values change
then add them or take them away from a another cell in a different column

Thankyou

look at multiple cells in a column and change a value
 

that helps alot thankyou very much , how would i do the same if my range is
on a different worksheet ? thankyou

"Toppers" wrote:

You could use an event macro.

Assume the range of interest is A1:A100 (adjust to suit) and cell to change
is C10.

Amend logic to determine add /subtraction

Select the sheet where you want this to happen.
Right click the sheet tab and select View Code.
Paste this code into the window that opens.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo ErrHandler
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:A100")) Is Nothing Then
Range("C10")= Range("C10")+Target.value
End If
ErrHandler:
Application.EnableEvents = True
End Sub

Hit ALT Q to return to Excel.

HTH

"RobG2007" wrote:

How can i look at multiple cells in a column and if any of the values change
then add them or take them away from a another cell in a different column

Thankyou

look at multiple cells in a column and change a value
 

This code needs to be on the same worksheet as the data that is changed;
hence why you have to select the tab and copy the code.

"RobG2007" wrote:

that helps alot thankyou very much , how would i do the same if my range is
on a different worksheet ? thankyou

"Toppers" wrote:

You could use an event macro.

Assume the range of interest is A1:A100 (adjust to suit) and cell to change
is C10.

Amend logic to determine add /subtraction

Select the sheet where you want this to happen.
Right click the sheet tab and select View Code.
Paste this code into the window that opens.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo ErrHandler
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:A100")) Is Nothing Then
Range("C10")= Range("C10")+Target.value
End If
ErrHandler:
Application.EnableEvents = True
End Sub

Hit ALT Q to return to Excel.

HTH

"RobG2007" wrote:

How can i look at multiple cells in a column and if any of the values change
then add them or take them away from a another cell in a different column

Thankyou