Budget

[Brad L]

I have total income for month in A1.

All of my budgeted expenses are listed in B3-B20, with ACTUAL expenses to be plugged in to respective C column cells (3-20).

As I plug in each ACTUAL expense into column C (as they come in throughout the month), I want them deducted from the income value in A1, and for A1 to show the new decremented value.

Thank you!

 

[Brad L]

I would also like to color the cells for each expense at the end of the month, GREEN for surplus, RED for deficit

 

[VoG]

Try this: right click the sheet tab, select View Code and paste in

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("C3:C20")) Is Nothing Then
    Application.EnableEvents = False
    Range("A1").Value = Range("A1").Value - Target.Value
    Application.EnableEvents = True
End If
End Sub

 

[VoG]

I would also like to color the cells for each expense at the end of the month, GREEN for surplus, RED for deficit

Try Conditional Formatting http://www.contextures.com/xlCondFormat01.html

 

[Brad L]

Thanks Peter!

 

[VoG]

Via PM

Hi Peter:

The following code works great, except that if I go back and delete an ACTUAL value from one of the C column expenses (ex:..if I made a mistake), it does NOT alter the A1 value (ie: it remains decremented)

Thanks so much!

Try changing the code to

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("C3:C20")) Is Nothing Then
    Application.EnableEvents = False
    If Target.Value = "" Then
        Application.Undo
        Range("A1").Value = Range("A1").Value + Target.Value
        Target.ClearContents
    Else
        Range("A1").Value = Range("A1").Value - Target.Value
    End If
    Application.EnableEvents = True
End If
End Sub