Can’t remember the code to use

[ipbr21054]

I have a specific cell which is J9

I have a spreadsheet of user information.

Now I cant remember the name of the code to use but when a name is entered into cell J9 this code would then look at the spreadsheet in column D for the same name & select its cell.

Thanks.

 

[ipbr21054]

Changing the items mentioned above still does the same thing & same line of code shown in yellow

 

[MARK858]

I don't see how you are getting the error on that line when the On error resume next line was there. Please upload a copy of your workbook to www.box.com, mark it for sharing and post the link it provides in the thread so I can see if the other code you added or your setup is affecting it.

 

[ipbr21054]

http://www.mediafire.com/file/q9x4ci4uazyysa7/MARK858.zip/file

Here we go

 

[MARK858]

I won't download from MediaFile as I don't know enough about the site to trust it, either upload to box as requested or www.dropbox.com

 

[ipbr21054]

See if this works for you https://www.dropbox.com/sh/grl6k999ykwf2u9/AAABQM0zneFrGESb-wvOhdbya?dl=0

 

[MARK858]

That appears to be fine, I will have a proper look at it once I have had some tea.

 

[ipbr21054]

No problem at all
Thanks.

 

[Rick Rothstein]

See if this works for you https://www.dropbox.com/sh/grl6k999ykwf2u9/AAABQM0zneFrGESb-wvOhdbya?dl=0

I downloaded it and opened it up in my copy of Excel and it errored out immediately in the Workbook Open event... your Workbook module has three event procedures in that module all of which reference a worksheet named "HONDA SHEET" but the workbook you posted does not have such a sheet in it, you only have two sheets ("DATABASE" and "INFO").

 

[ipbr21054]

All that code does is open Honda sheet and go to cell A13 I believe.

I deleted the other sheets and just uploaded these two.

 

[MARK858]

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r As Range

    If Not Intersect(Target, Range("E3")) Is Nothing Then
        If Target.Value <> "" And Target.Cells.Count = 1 Then
            Application.EnableEvents = False
            On Error GoTo errHandle2
            Columns("A:A").Find(Target.Value, , , xlWhole, , xlNext).Select
            On Error GoTo 0
            Application.EnableEvents = True
        End If
    End If

    On Error GoTo errHandle

    Application.EnableEvents = False
    Application.ScreenUpdating = False

    If Target.Address = "$A$6" Then
        With Sheets("INFO").Range("CG2")
            If Len(.Offset(1).Value) Then
                Set r = .End(xlDown).Offset(1)
                With .End(xlDown).Offset(1)
                    .Value = UCase$(ActiveSheet.Cells(6, 1).Value)
                    .Interior.ColorIndex = 6
                    .HorizontalAlignment = xlCenter
                    .VerticalAlignment = xlBottom
                    .VerticalAlignment = xlCenter
                    .Borders.LineStyle = xlContinuous
                    .RowHeight = 19.5
                    .Font.Bold = True
                    With ActiveWorkbook.Worksheets("INFO").Sort
                        .SetRange Range("CG2:CG500")
                        .Header = xlYes
                        .MatchCase = False
                        .Orientation = xlTopToBottom
                        .SortMethod = xlPinYin
                        .Apply
                    End With
                End With
            End If
        End With
    End If

    With Target
        If .Column <> 13 And .Count = 1 And Not .HasFormula Then




            .Value = UCase$(.Value)
        End If
    End With

    Application.EnableEvents = True
    Application.ScreenUpdating = True

    Exit Sub
errHandle2:
    MsgBox "Name not found"
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Exit Sub

errHandle:
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    MsgBox Err.Description, vbCritical, "Error number: " & Err.Number

End Sub