כיצד לבצע חיפוש וסימון (VLOOKUP) ולהחזיר את צבע הרקע יחד עם ערך החיפוש ב-Excel?

נניח שיש לך טבלה כפי שמוצג בסcreenshot למטה. כעת ברצונך לבדוק אם ערך מסוים נמצא בעמודה A ולאחר מכן להחזיר את הערך המתאים לו יחד עם צבע הרקע שלו בעמודה C. כיצד ניתן לעשות זאת? השיטה המוצגת במאמר זה יכולה לעזור לך לפתור את הבעיה.

חיפוש וסימון והחזרת צבע רקע עם ערך החיפוש באמצעות פונקציה מותאמת אישית

חיפוש וסימון והחזרת צבע רקע עם ערך החיפוש באמצעות פונקציה מותאמת אישית

אנא עשו את הפעולות הבאות כדי לחפש ערך ולהחזיר את הערך המתאים לו יחד עם צבע הרקע ב-Excel.

1. בגיליון העבודה שמכיל את הערך אותו ברצונכם לחפש, לחצו לחיצה ימנית על לשונית הגליון ובחרו תצוגה קוד מתוך התפריט המקוצר. ראו צילום מסך:

2. בחלון Microsoft Visual Basic for Applications שנפתח, אנא העתיקו את הקוד VBA הבא לחלון הקוד.

קוד VBA 1: חיפוש וסימון והחזרת צבע רקע עם ערך החיפוש

Sub Worksheet_Change(ByVal Target As Range)
    Dim I As Long
    Dim xKeys As Long
    Dim xDicStr As String
    On Error Resume Next
    Application.ScreenUpdating = False
    xKeys = UBound(xDic.Keys)
    If xKeys >= 0 Then
        For I = 0 To UBound(xDic.Keys)
            xDicStr = xDic.Items(I)
            If xDicStr <> "" Then
                Range(xDic.Keys(I)).Interior.Color = _
                Range(xDic.Items(I)).Interior.Color
            Else
                Range(xDic.Keys(I)).Interior.Color = xlNone
            End If
        Next
        Set xDic = Nothing
    End If
    Application.ScreenUpdating = True
End Sub

3. לאחר מכן לחצו על הוסף > מודול, והעתקו את הקוד VBA 2 הבא לחלון המודול.

קוד VBA 2: חיפוש וסימון והחזרת צבע רקע עם ערך החיפוש

Public xDic As New Dictionary
Function LookupKeepColor (ByRef FndValue, ByRef LookupRng As Range, ByRef xCol As Long)
    Dim xFindCell As Range
    On Error Resume Next
    Set xFindCell = LookupRng.Find(FndValue, , xlValues, xlWhole)
    If xFindCell Is Nothing Then
        LookupKeepColor = ""
        xDic.Add Application.Caller.Address, ""
    Else
        LookupKeepColor = xFindCell.Offset(0, xCol - 1).Value
        xDic.Add Application.Caller.Address, xFindCell.Offset(0, xCol - 1).Address
    End If
End Function

4. לאחר הכנסת שני הקודים, לחצו על כלים > הפניות. לאחר מכן סמנו את תיבת Microsoft Script Runtime בחלון הדו-שיח הפניות – פרויקט VBA. ראו צילום מסך:

5. לחצו על המקשים Alt + Q כדי לצאת מחלון Microsoft Visual Basic for Applications וחזרו לגיליון העבודה.

6. בחרו תא ריק סמוך לערך החיפוש, ולאחר מכן הזינו את הנוסחה =LookupKeepColor(E2,$A$1:$C$8,3) לתוך שורת הנוסחאות, ולאחר מכן לחצו על מקש Enter.

הערה: בנוסחה, E2 מכיל את הערך אותו תחפשו, $A$1:$C$8 הוא טווח הטבלה, והמספר 3 אומר שהערך המתאים אותו תרצו להחזיר נמצא בעמודה השלישית של הטבלה. אנא שנה אותם לפי הצורך.

7. המשיכו לבחור בתא התוצאה הראשון, וגררו את הידית למלא עד לקבלת כל התוצאות יחד עם צבע הרקע שלהן. ראו צילום מסך.

מאמרים קשורים:

• כיצד להעתיק את עיצוב המקור של תא החיפוש בעת שימוש ב-Vlookup ב-Excel?
• כיצד לבצע חיפוש וסימון (VLOOKUP) ולהחזיר תבנית תאריך במקום מספר ב-Excel?
• כיצד להשתמש בחיפוש וסימון (VLOOKUP) וכפל ב-Excel?
• כיצד לבצע חיפוש וסימון (VLOOKUP) ולהחזיר ערך בתא הסמוך או הבא ב-Excel?
• כיצד לבצע חיפוש וסימון (VLOOKUP) ולהחזיר אמת או שקר / כן או לא ב-Excel?