# Solved how do i subtract from a constant cell in excel

May 30, 2016 at 04:34:40
Specs: Windows 7
 In Cell A1 I have my daily target say 1000when i enter number in a2,a3,a4 etc the number should get deducted automatically. for exampleI have 1000 in A1 when i enter 50 in A2 it should give me 950 and when i enter 70 in a3 it should give me 880 (1000-50-70) and when i reach 0 it should give me a message target achieved. See More: how do i subtract from a constant cell in excel

#1 May 30, 2016 at 07:04:38
 I can think of 2 methods, one using VBA (a macro) and the other using Conditional Formatting.Macro Method: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 End If End Sub ```Conditional Formatting Method1 - 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.` =B1-SUM(A2:A100)`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:=A1<=0 