Worksheet_change acting on new active cell instead of original

[Eagle72]

Hi,
I have written a macro that automatically assigns the next available project number once a new project title is entered. It works great if I type in the new title and press Enter. However, if I tab over to the next cell instead, it puts the new project number in the wrong cell (1 cell over). It also puts the new project number one cell too low if the "Move selection after enter" is turned on in Excel. I want the macro to always put in the new project number 1 cell to the left of the original active cell. Any help would be greatly appreciated. I'm using XP and Excel 2003. Oh, I tried grabbing the address of the active cell before any of the code lines, but since this macro doesn't run until there is a change in column 'C', it grabs the new active cell address, not the original. Here is the code:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("C3:C10000")) Is Nothing Then
If ActiveCell.Offset(0, -2).Value = "" Then
Set myRange = Worksheets("Sheet1").Range("A3: A10000 ")
Answer = Application.WorksheetFunction.Max(myRange)
ActiveCell.Offset(0, -2).Value = Answer + 1
End If
End If

End Sub


Thanks!

 

[Eagle72]

I need to clarify slightly. I meant to say that i want the project number 2 cells to the left of the original active cell. For the code I've written, the project number goes in column A and the project description goes in column C. Right now, if I type in a new description in cell C5 and tab over to D5, the new project number gets put in cell B5 instead of A5. If the "Move selection after enter" is turned on in Excel, after typing in a new description in cell C5 and pressing Enter, the active cell becomes C6 and the new project number gets put in A6.