auto number

Welcome to the Board!

This might work for you:

<font face=Tahoma><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)
    <SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range
    <SPAN style="color:#007F00">'   Only look at single cell changes</SPAN>
    <SPAN style="color:#00007F">If</SPAN> Target.Count > 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
    <SPAN style="color:#007F00">'   Assumes you select the Product Code in Cell A2</SPAN>
    <SPAN style="color:#00007F">Set</SPAN> rng = Range("A2")
    <SPAN style="color:#007F00">'   Only look at that range</SPAN>
    <SPAN style="color:#00007F">If</SPAN> Intersect(Target, rng) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
    <SPAN style="color:#007F00">'   Increment Cell B2's current value by 1</SPAN>
    Range("B2").Value = Range("B2").Value + 1
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

The code goes in the worksheet specific module. Change the ranges as necessary.

Hope that helps,

Smitty

Altough in this case it doesn't create an endless loop, it's a good idea to disable events to prevent infinite recursion, i.e, change this:



to

Good catch Juan! Thanks!

And congratulations on 9000! Quite impressive.

Smitty

A formula approach might do:

=IF(H182<>"",ROW()+3500,"")

adjust "3500" until it matches your tracking numb requirements

Am i being overly simplistic by saying put

=A32+1 in cell A33? Then the formula could simply be extended as needed.

hi
thanks for the quick replys i don't think i've explainned this right
new to this!
i know this code does'nt work on the line in red
but maybe you can see what i'm trying to do
when i enter data in col3 it's puts a date stamp in col1 and increments
the number by 1 from the previous cell in col 2
can u help me with the code
thanks

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 3 Then
Intcolumn = 1
introw = Target.Row
Cells(introw, Intcolumn) = Now()

End If

If Target.Column = 1 Then
Intcolumn = 2
introw = Target.Row
Cells(introw, Intcolumn) = "=IF(H182<>"",ROW()+3455,"")"

End If

End Sub

Did you want something like this?

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 3 Then
Cells(Target.Row, 1) = Now()
Cells(Target.Row, 2) = Target.Row + 3455
End If
End Sub

great
thanks everyone