Excel cell changes question

[doris_day]

I want to be able to show in one cell changes in another cell due to a refresh. For instance, say cell B5 has 3.0 in it and then on a refresh this changes to 3.2, I would like C5 (say) to show 0.2 i.e the change from 3.0 to 3.2. If on the next refresh the cell changed from 3.2 to 3.0 again, cell C5 should show -0.2.
I'm sure there must be a simple function for this so any help would be appreciated.

[Roger]

I think you need to use VBA to do this.

[doris_day]

Would you have an example of the code I'd need to write ?

[alrodopial]

Can you change the code given here?

http://gruss-software.co.uk/forum/viewtopic.php?t=3265

[doris_day]

Thanks. I'll take a closer look but it seems that this code records the data but I only need to know the change from one refresh to another. But if I look closer I may be able to adapt it.
Thanks for the idea.

[Roger]

Sorry I only really knew it couldn't be done with formulas.

[alrodopial]

Copy paste the below in VBA-Sheet1 (top left in VBA editor) (not module).
It records the value from cell C5 in sheet1 at cell D5-sheet1 and places their difference in E10 in sheet2.You must log prices from BA into sheet1.
Not tested.
Change the cells as you wish.

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
   
    If Target.Columns.Count = 16 Then
       
        Application.EnableEvents = False
       
        CurrentPRICE = Sheets("Sheet1").Range("C5").Value
        PreviusPRICE = Sheets("Sheet1").Range("D5").Value
       
        Sheets("sheet2").Range("E10") = CurrentPRICE - PreviusPRICE
       
        'Store the current price for next refresh - calculation
        Sheets("sheet1").Range("D5") = Sheets("sheet1").Range("C5")
       
        Application.EnableEvents = True
       
    End If
   

[doris_day]

Thanks for that. For some strange reason, and possibly because I have data being read from 2 tabs in BA which go to Sheet 1 and Sheet 2, after I've done the VBA stuff, it doesnt seem to recognise Sheet 1 in BA at all, so I cant route any data to it. I've changed all the relevant cell names of course but I'll have a futher play a little later on.
Any ideas ?

[alrodopial]

Works for me:

http://www.mediafire.com/?sharekey=0088 ... 49b5870170

[doris_day]

Thanks for that. Yes, your file seems to work fine, even after changing cells etc. There must be some VBA conflict in my worksheet somewhere that I'm looking into. If I cant get round it, I'll rebuild a worksheet around yours.
Thanks v much as this has shown me what I need to do.
This is how a good forum should work !