Excel 2007 : Can I set up a cell to subtract other cells if the value in that cell is over 0

I have a situation where I need to be able to enter a number in a cell and have it subtract other cells automatically, but only if that number is greater than zero. I assume I'll need to set up some vb script to make this work the way I want, because any time I enter a number into a cell, the formula in that cell is deleted.

Any idea what I can do? Any help is appreciated.

Shaun

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1"      '<<<< change to suit

    On Error GoTo ws_exit
    
    Application.EnableEvents = False
    
    If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
    
        With Target
        
            If .Value > 0 Then
            
                Me.Range("A1").Value = Me.Range("A1").Value - .Value
                Me.Range("B1").Value = Me.Range("B1").Value - .Value
                Me.Range("C1").Value = Me.Range("C1").Value - .Value
                Me.Range("N1").Value = Me.Range("N1").Value - .Value
                'etc.
        End With
    End If

ws_exit:
    Application.EnableEvents = True
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.

[quote=shaunn1812;2578948]Thanks for that Bob. Just one question though (still getting the hang of vb). What exactly does the WS_RANGE refer to?/quote]

WS_RANGE is just a string constant I use to define the range of cells to be checked. Just change it to whatever cells apply for you.