كيفية نسخ تنسيق المصدر لخلية البحث عند استخدام Vlookup في Excel؟

في المقالات السابقة ، تحدثنا عن الحفاظ على لون الخلفية عند قيم vlookup في Excel. هنا في هذه المقالة ، سنقدم طريقة لنسخ جميع تنسيقات الخلية للخلية الناتجة عند إجراء Vlookup في Excel. الرجاء القيام بما يلي.

نسخ تنسيق المصدر عند استخدام Vlookup في Excel مع وظيفة محددة من قبل المستخدم

نسخ تنسيق المصدر عند استخدام Vlookup في Excel مع وظيفة محددة من قبل المستخدم

رائعة حقا! استخدام علامات تبويب فعالة في Excel مثل Chrome و Firefox و Safari!

وفر 50٪ من وقتك ، وقلل آلاف النقرات بالماوس كل يوم!

لنفترض أن لديك جدولًا كما هو موضح أدناه. أنت الآن بحاجة إلى التحقق مما إذا كانت القيمة المحددة (في العمود E) موجودة في العمود A وإرجاع القيمة المقابلة بالتنسيق في العمود C. يرجى القيام بما يلي لتحقيق ذلك.

1. في ورقة العمل تحتوي على القيمة التي تريد مشاهدتها ، انقر بزر الماوس الأيمن فوق علامة تبويب الورقة وحدد عرض الرمز من قائمة السياق. انظر لقطة الشاشة:

2. في الافتتاح ميكروسوفت فيسوال باسيك للتطبيقات نافذة ، يرجى نسخ رمز VBA أدناه في نافذة التعليمات البرمجية.

كود VBA 1: Vlookup وقيمة الإرجاع مع التنسيق

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?

---

The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

• Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
• Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
• Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
• Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
• Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
• Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
• Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
• Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
• More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.

Read More... Free Download... Purchase... 

---

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

• Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
• Open and create multiple documents in new tabs of the same window, rather than in new windows.
• Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!

Read More... Free Download... Purchase...