Pasting (Source) VLOOKUP Formatting - 🙏 www.extendoffice.com 🤓🤘

Just before we start, may I recommend the source of below content for all MS Office niggles & some decent tips!

If you're anything like me you've got some ludicrous work-arounds for those frustrating #Excel roadblocks - the ones that you know there's got to be a way to do it!!
"they just wouldn't build it like that" - would they?

extendoffice.com sends me to bed earlier🤣

There's often a few hours lost to writing those macros that you just KNOW there's a f(*&ing menu option for but you just can't find it...... = f*&k it, I'm recording a macro 🤓

oooooooooooooooooooor check it out - it's really rather handy! cheers guys 👊👏 

PS: Should you be affiliated with www.extendoffice.com thanks for helping me get my head round a very old bug-bear!....(oh & please do get in touch immediately should you not appreciate this plagiaristic ode to your brilliance and I'll take it down!😬🤦‍♂️🤣)

AAAAAAAAAAAAAAAAAANYHOOO - here's a solution that was quite hard to find so I thought I'd share it!..... 

"How To Copy Source Formatting Of The Lookup Cell When Using Vlookup In Excel?

In the previous articles, we have talked about keeping background color when vlookup values in Excel. Here in this article, we are going to introduce a method of copying all cell formatting of the resulting cell when doing Vlookup in Excel. Please do as follows.

Copy source formatting when using Vlookup in Excel with a User-defined function

---

 Copy Source Formatting When Using Vlookup In Excel With A User-Defined Function

Supposing you have a table as below screenshot shown. Now you need to check if a specified value (in column E) is in column A and return corresponding value with formatting in column C. Please do as follows to achieve it.

1. In the worksheet contains the value you want to vlookup, right-click the sheet tab and select View Code from the context menu. See screenshot:

2. In the opening Microsoft Visual Basic for Applications window, please copy below VBA code into the Code window.

VBA code 1: Vlookup and return value with formatting

Sub Worksheet_Change(ByVal Target As Range)
'Update by Extendoffice 20211203
    Dim I As Long
    Dim xKeys As Long
    Dim xDicStr As String
    On Error Resume Next
    Application.ScreenUpdating = False
    Application.CutCopyMode = False
    xKeys = UBound(xDic.Keys)
    If xKeys >= 0 Then
        For I = 0 To UBound(xDic.Keys)
            xDicStr = xDic.Items(I)
            If xDicStr <> "" Then
                Set xRg = Application.Range(xDicStr)
                xRg.Copy
                Range(xDic.Keys(I)).PasteSpecial xlPasteFormats
            Else
                Range(xDic.Keys(I)).Interior.Color = xlNone
            End If
        Next
        Set xDic = Nothing
    End If
    Application.ScreenUpdating = True
    Application.CutCopyMode = True
End Sub

3. Then click Insert > Module, and copy the below VBA code 2 into the Module window. VBA code 2: Vlookup and return value with formatting Public xDic As New Dictionary 'Update by Extendoffice 20211203 Function LookupKeepFormat(ByRef FndValue, ByRef LookupRng As Range, ByRef xCol As Long) Dim xFindCell As Range On Error Resume Next Application.ScreenUpdating = False Set xFindCell = LookupRng.Find(FndValue, , xlValues, xlWhole) If xFindCell Is Nothing Then LookupKeepFormat = " " xDic.Add Application.Caller.Address, " " Else LookupKeepFormat = xFindCell.Offset(0, xCol - 1).Value xDic.Add Application.Caller.Address, xFindCell.Offset(0, xCol - 1).Address(External:=True) End If Application.ScreenUpdating = True End Function 4. Click Tools > References. Then check the Microsoft Script Runtime box in the References – VBAProject dialog box. See screenshot: 5. Press the Alt + Q keys to exit the Microsoft Visual Basic for Applications window. 6. Select a blank cell adjacent to the lookup value, and then enter formula =LookupKeepFormat(E2,$A$1:$C$8,3) into the Formula Bar, and then press the Enter key. Note: In the formula, E2 contains the value you will lookup, $A$1:$C$8 is the table range, and number 3 means that the corresponding value you will return locates in the third column of the table. Please change them as you need. 7. Keep selecting the first result cell, and then drag the Fill Handle down to get all results along with their formatting as below screenshot showed. Related articles: How to vlookup and return background color along with the lookup value in Excel? How to vlookup and return date format instead of number in Excel? How to use vlookup and sum in Excel? How to vlookup return value in adjacent or next cell in Excel? How to vlookup value and return true or false / yes or no in Excel? .kte-bottom-general { font-family: arial; #border: solid 1px #fff; } .kte-bottom-general img { display: block; } .tex-content { margin-right: 10px; }

"