Excel VBA Function for Value AND Format Lookup

formattingmicrosoft excelvbaworksheet-function

I need a custom function to lookup both a cell value and copy the source format. I have a list of values in two columns. The first is of integers and the second column is the corresponding text values. For example:

A B
— ———-
1 My first value
2 My second value
3 My third value

I want to lookup the value I provide as it corresponds to those in column A. Based on the row number of the cell found with the matching value in column A, it will select the corresponding value in column B. This is easy enough with the LOOKUP() function in Excel. However, I want to ALSO copy the text formatting of the cell in column B.

Initially I approached this using a VBA function but unfortunately a function cannot change the formatting of a cell. Would appreciate ideas on how to approach.

Best Answer

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
Related Question