Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Finding negative and positive diffrence
I want to put in a number in a cell which will change everyday, in a next
cell I want to see postive or negative diffrence, e.g. first cell may contain 105.90 and next day if it changes to 105.80 than next cell should show -.10, and it it is negative cell should turn red if it is positive it should turn green in background color. Any suggestions? Thank you. |
#2
|
|||
|
|||
Let us say you put 105.9 in A1 today. Tomorrow you type 105.8 in A1.
Now how do you expect Excel to recall what was in the cell before you typed the new value? I think the problem must be re-cast. best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Ashish Doshi" <Ashish wrote in message ... I want to put in a number in a cell which will change everyday, in a next cell I want to see postive or negative diffrence, e.g. first cell may contain 105.90 and next day if it changes to 105.80 than next cell should show -.10, and it it is negative cell should turn red if it is positive it should turn green in background color. Any suggestions? Thank you. |
#3
|
|||
|
|||
Bernard Liengme wrote...
Let us say you put 105.9 in A1 today. Tomorrow you type 105.8 in A1. Now how do you expect Excel to recall what was in the cell before you typed the new value? I think the problem must be re-cast. .... Agreed that this is not something spreadsheets are intended to do, but it can be done using event handlers. First, select all cells that should be 'rememberred' and give them the worksheet-level defined name SAVE. So if you need to use the previous values of cells C5 and F7 in worksheet FOO, select FOO!C5 and FOO!F7 and define the name FOO!SAVE referring to =(FOO!$C$5,FOO!$D$7). Then enter the following in the FOO worksheet's class module. Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range, v As Variant On Error GoTo CleanUp Application.EnableEvents = False Application.ScreenUpdating = False Set rng = Intersect(Target, Me.Names("SAVE").RefersToRange) If Not rng Is Nothing Then v = Target.Value Application.Undo Me.Names.Add Name:="SAVE_" & Target.Address(0, 0), RefersTo:=Target.Value Target.Value = v End If CleanUp: Application.ScreenUpdating = True Application.EnableEvents = True End Sub Use formulas like FOO!D5: =C5-SAVE_C5 to calculate the differences and use conditional formatting to change the background color. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do I rank negative and positive numbers? | Excel Worksheet Functions | |||
How can I change positive numbers to negative, i.e. change 50 to - | Excel Discussion (Misc queries) | |||
How to vary color in an area chart between negative or positive d. | Charts and Charting in Excel | |||
Erf (the error function) for both negative and positive numbers | Excel Discussion (Misc queries) | |||
Negative numbers turn positive automatically on data entry | Excel Discussion (Misc queries) |