|I can think of 2 methods, one using VBA (a macro) and the other using Conditional Formatting.|
Right click the sheet tab for the sheet you want this to happen in. Paste this code into the pane that opens. Whenever you enter a value in A2 or below, the SUM of the values below A1 will be subtracted from 1000 and the value will show in A1.
Private Sub Worksheet_Change(ByVal Target As Range)
'Determine if change was made to Column A
If Target.Column = 1 Then
Application.EnableEvents = False
'Determine how many values are in Column A and Subtract SUM from A1
lastRw = Range("A" & Rows.Count).End(xlUp).Row
Range("$A$1") = 1000 - WorksheetFunction.Sum(Range("$A$2:$A" & lastRw))
'Display message if A1 <= 0
If Range("$A$1") <= 0 Then MsgBox "Goal Reached"
Application.EnableEvents = True
Conditional Formatting Method
1 - Place 1000 in a cell other than A, e.g. B1. You can hide the cell or change the text to White if you don't want to see the 1000.
2 - Enter this formula in A1 if using B1. Adjust to fit your situation.
3 - Enter your message in a cell that will always be within the viewable area of your screen, e.g. C1.
4 - Format the cell as White text to hide the message.
5 - Conditionally format the cell using the following formula, and use a format to fill the cell with a color of your choice. When the formula is TRUE, the White text will appear within the filled background:
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.