Excel Script help

[xxdylantxx]

I have this script that changes the value of one cell by whatever increment or detriment is done to a second cell. IE if I have 32 and expend 1 the first cell goes to 31. If I expend 2 more the first cell goes to 29. I am trying to figure out how I can expand this script to make it work for 2 more cells that are independant of these 2 cells but work the same way. I have tried to add more cells as ranges and put them into the script but it hasn't worked any way I have tried it. Any ideas?


Private Sub Worksheet_Change(ByVal Target As Range)
Dim firstCell As Range, secondCell As Range, nV#, oV#
Set firstCell = [A1]
Set secondCell = [A2]
Application.EnableEvents = False
If Not Intersect(Target, firstCell) Is Nothing Then
If Target.Cells.Count > 1 Then
MsgBox firstCell.Address(0, 0) & " cannot be. changed at the same time. "
Application.Undo
GoTo e
End If
nV = firstCell
Application.Undo
oV = firstCell
firstCell = nV
If nV > oV Then secondCell = secondCell - nV + oV
End If
e: Application.EnableEvents = True
End Sub

 

[Joe4]

Welcome to the Board!

Which cells do you want it to run against?

 

[xxdylantxx]

Welcome to the Board!

Which cells do you want it to run against?

I'm using B8 and B9 together and for the second set I believe its A8 and A9. But I can fill in those values if you can point me in the right direction script wise.

 

[Joe4]

See if this does what you want:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim watchRng As Range, nV#, oV#
    
    Set watchRng = Range("A8:B8")

    Application.EnableEvents = False

    If Not Intersect(Target, watchRng) Is Nothing Then
        If Target.Cells.Count > 1 Then
            MsgBox Target.Address(0, 0) & " cannot be. changed at the same time. "
            Application.Undo
            GoTo e
        End If
        nV = Target.Value
        Application.Undo
        oV = Target.Value
        Target = nV
        If nV > oV Then Target.Offset(1, 0) = Target.Offset(1, 0) - nV + oV
    End If
    
e:
    Application.EnableEvents = True

End Sub

 

[xxdylantxx]

I'm using B8 and B9 together and for the second set I believe its A8 and A9. But I can fill in those values if you can point me in the right direction script wise.

See if this does what you want:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim watchRng As Range, nV#, oV#
   
    Set watchRng = Range("A8:B8")

    Application.EnableEvents = False

    If Not Intersect(Target, watchRng) Is Nothing Then
        If Target.Cells.Count > 1 Then
            MsgBox Target.Address(0, 0) & " cannot be. changed at the same time. "
            Application.Undo
            GoTo e
        End If
        nV = Target.Value
        Application.Undo
        oV = Target.Value
        Target = nV
        If nV > oV Then Target.Offset(1, 0) = Target.Offset(1, 0) - nV + oV
    End If
   
e:
    Application.EnableEvents = True

End Sub

It wouldn't work cause there is other information within those bounds that would be affected. The document isn't something I can manipulate to work with that unfortunately. It's sent to me already but I can plug in scripts to make it easier. That's why I was hoping there was a way to incorporate the same effect to 2 separate cells to work independently but work the same way. I appreciate your help though. The cells I need to work together are B9, B8 & D9, D8

 

[Joe4]

It wouldn't work cause there is other information within those bounds that would be affected. The document isn't something I can manipulate to work with that unfortunately. It's sent to me already but I can plug in scripts to make it easier. That's why I was hoping there was a way to incorporate the same effect to 2 separate cells to work independently but work the same way. I appreciate your help though. The cells I need to work together are B9, B8 & D9, D8

OK, you changed it up on me. You had said A8,A9 and B8:B9.

And I am guessing that you might not understand what the changes I made to your code actually do.
Note this line of code here:

    Set watchRng = Range("A8:B8")

and then this line here:

    If Not Intersect(Target, watchRng) Is Nothing Then

Basically, what that is doing is looking for a change in either cell A8 or B8, and then running the code (independently of the other).
So a change to A8 would only affect A8 and A9.
And a change to B8 would only affect B8 and B9.
That is what you want, is it not?

If you want it to be triggered to run on changes to cells B8 and D8, then change this line:

    Set watchRng = Range("A8:B8")

to

    Set watchRng = Range("B8, D8")