VBA functions can change the value of "another cell" they really can do just
about anything.

However what you want here is code in your Workbook_SheetChange event
From VBE choose thisworkbook
pulldown Sheetchange
insert code

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
dim myTargetCellOne as range
dim myTargetCellTwo as range
dim myTargetCellThree as range

set myTargetCellOne = 'cell1 address here
set myTargetCellTwo = 'cell2 address here
set myTargetCellThree = 'cell3 address here

If Target = myTargetCellOne Then
If myTargetCellOne.value = "" Then
myTargetCellThree.clearcontents
Exit Sub
Else myTargetCellThree.value = myTargetCellTwo.value
end if
end if

End Sub

"Tom" wrote:

Hi,

I've recently started writing functions in Excel which I then call from
cells to perform calculations etc. However, I am aware that a function
cannot alter the value of *another* cell. What I want to do is this :

<Cell 1 is usually empty.
When <Cell 1 changes from empty to any other value, take a snapshot of
<Cell 2 (which updates constantly) and copy it into <Cell 3.
<Cell 2 will then continue to update as usual, whilst <Cell 3 will contain
a static value.
Then when <Cell 1 becomes empty again, erase <Cell 3 and that's it.
Then keep repeating this whole process as long as the spreadsheet is open.

The concept sounds simple enough and I'm sure it must be possible but I
can't for the life of me figure out how! All I've been using so far is
functions, which take arguments and return values. Any help appreciated!

Many thanks,

Tom