Finding negative and positive diffrence

[Guest]

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.

 

[Bernard Liengme]

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

 

[Harlan Grove]

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.