Excel VBA Function for Value AND Format Lookup

Solution 1:

Sub test()
    On Error GoTo Suberror
    'turn off screen updating
    Application.ScreenUpdating = False
    'ask for input
    strName = InputBox(Prompt:="Lookup Value", _
              Title:="Lookup Value", Default:="0")
        'if no input - exit
        If strName = "0" Or _
               strName = vbNullString Then

               Exit Sub
        'otherwise Find
        Else

            Columns("A:A").Select
            Selection.Find(What:=strName, After:=ActiveCell, _
            LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
            SearchDirection:=xlNext, MatchCase:=False, _
            SearchFormat:=False).Activate

            'Copy
            ActiveCell.Offset(0, 1).Copy

        End If

        'Paste to the range that you define
            Range("J1").Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:= _
                xlNone, SkipBlanks:=False, Transpose:=False
            Selection.PasteSpecial Paste:=xlPasteFormats, Operation:= _
                xlNone, SkipBlanks:=False, Transpose:=False
Suberror:
    Range("A1").Select
    Application.CutCopyMode = False
    Application.ScreenUpdating = True

    End Sub