Dear all,

I have two worksheets. One is the input, the other holds a list of 100s of names (ultimatley it will be two separate work books because the input workbook will be started from zero each year, but the list sheet will grow... but step after step.. as I assume that debuging is easier within the same workbook first and I don't have the skills and understanding to skip this step with confidence).

I know that I could copy/link all the names into the cells above my input cells and hide them, but I don't think that is a very clean way.

Googeling a solution I found this
http://www.mrexcel.com/forum/showthread.php?t=31107
for a text field and tried to adapt it to a cell. But for some reason I am not able to get it to run.

Can you please help me understand where I am wrong?

Option Explicit
Dim oRange As Range
Dim iCharCount As Integer
Dim strAuto As String
Dim sTemp As String

Private Sub Worksheet_Change(ByVal Target As Range)
' to which cell changes to react (this works fine)
    If Not Intersect(Target, Range("B2:C10000")) Is Nothing Then ' if intersect is nothing it would be out of range
    If Range("B10000").End(xlUp).Row = Range("C10000").End(xlUp).Row Then
        Range("F1").Formula = Range("B10000").End(xlUp).Row
    End If
    ' here starts the autocomplete
    Set oRange = Worksheets("list").Range("a2").End(xlUp).Offset(999, 0) 'select from A2 to A1000
    oRange.Value = Target.Text
    strAuto = oRange.AutoComplete(Target.Text)
    If Len(strAuto) > 0 Then
        With Target
            sTemp = .Text
            .Text = strAuto
            .SelStart = Len(sTemp)
            .SelLength = Len(strAuto)
        End With
    End If
    MsgBox Target.Text
    oRange.ClearContents
    End If
End Sub
Any help is very much appreciated.