Hi Patrick,

The following code is the only way I have been able to reference the active
cell on a sheet which I deactivate. If anyone else reads this and has other
options then I am certainly interested.

My method involves the following:-

Saving the name of the new sheet.
Re-activating the old sheet.
Assign the active cell to a variable.
Turn off events .
Re-select the new required worksheet.

Also your code 'rng = Cells(ActiveCell.Row, 1)'. If you want to assign a
range to a variable then it should have Set in front of it:-
Set rng = Cells(ActiveCell.Row, 1)

To put the following code in the correct place, right click on the worksheet
name (tab) that is being deactivated and select View code. The VBA editor
will open in the right place.

Private Sub Worksheet_Deactivate()
Dim Rng As Range
Dim strNewSht As String

'Following line is for info only confirming the the new sheet
'is the active sheet before any of the code is processed.
MsgBox "Active Sheet is: " & ActiveSheet.Name

'Save the new sheet name
strNewSht = ActiveSheet.Name

'Select the original sheet
Sheets("Sheet1").Select

'Assign required range to a variable

Set Rng = Cells(ActiveCell.Row, 1)

'Disable events and re-select the new sheet
Application.EnableEvents = False
Sheets(strNewSht).Select
'Re-enable events
Application.EnableEvents = True

'Can now use the range as a reference to
'manipulate dat on the deactivated sheet
If Rng.Offset(0, 1) = "" Then
Rng.Clear
End If

End Sub

Regards,

OssieMac