İpucu: Digər dillər Google-tərcümə olunur. Sizi ziyarət edə bilərsiniz English bu linkin versiyası.
Daxil ol
x
or
x
x
Qeydiyyatdan
x

or

Excel-də axtarış dəyəri ilə birlikdə vlookup və fon rəngini necə qaytarmaq olar?

Aşağıda göstərilən ekran görüntüsü kimi bir masanız olduğunu düşünürəm. İndi müəyyən bir dəyər A sütununda olub-olmadığını yoxlamaq və daha sonra müvafiq qiyməti sütun C rəngli arxa fon ilə qaytarmaq istəyirsiniz. Məqədəki metod bu problemi həll etməyə kömək edə bilər.

Vlookup və istifadəçi müəyyən funksiyası ilə axtarış dəyəri ilə geri fon rəngini qaytarır


Vlookup və istifadəçi müəyyən funksiyası ilə axtarış dəyəri ilə geri fon rəngini qaytarır


Excel-də arka plan rəngləri ilə yanaşı, dəyəri araşdıran və müvafiq dəyərini qaytarmaq üçün xahiş edirik.

1. Çalışma kitabında vlookup istədiyiniz qiyməti ehtiva edir, hesabatı sekmesini sağ basın və seçin Kodları baxın kontekst menyusundan. Ekran görüntüsünə baxın:

2. Açılışda Proqramlar üçün Microsoft Visual Basic Pəncərədən, VBA kodu aşağıda Kod pencerəsinə kopyalayın.

VBA kodu 1: Vlookup və axtarış qiyməti ilə fon rəngini qaytarın

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. Sonra basın Taxmaq > Modules, və 2-un aşağıdakı VBA kodunu Modul pəncərəsinə kopyalayın.

VBA kodu 2: Vlookup və axtarış qiyməti ilə fon rəngini qaytarın

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. Iki kodları daxil etdikdən sonra basın Tools > References. Sonra yoxlayın Microsoft Script Runtime qutusunda Referanslar - VBAProject dialoq qutusu. Ekran görüntüsünə baxın:

5. Basın Dayandırmaq + Q çıxmaq üçün düymələr Proqramlar üçün Microsoft Visual Basic pəncərə və iş səhifəsinə geri qayıdın.

6. Axtarış dəyərinə bitişik bir boş hüceyrəni seçin və sonra formula daxil edin =LookupKeepColor(E2,$A$1:$C$8,3) Formula Çubuğuna daxil edin və sonra Enter düyməsini basın.

Qeyd: Formula, E2 axtarış edəcək dəyərini ehtiva edir, $ A $ 1: $ C $ 8 masa aralığı və sayıdır 3 masanın üçüncü sütununda tapacaqsınız. Lazım olduğunuz halda onları dəyişdirin.

7. İlk nəticə hücresini seçməyi davam etdirin və bütün nəticələrini fon rəngi ilə birlikdə əldə etmək üçün Doldurma Tutumu aşağı sürüşdürün. Ekran görüntüsünə baxın.


Related articles:



Təklif olunan Məhsuldarlıq Vasitələri

Office Tab

qızıl star1 Chrome, Firefox və yeni Internet Explorer kimi Excel və digər Office proqramlarına lazımlı sekmeleri gətirin.

Excel üçün Kutools

qızıl star1 Amazing! 5 dəqiqədə məhsuldarlığınızı artırın. Heç bir xüsusi bacarıqa ehtiyac yoxdur, hər gün iki saat saxlaya bilərsiniz!

qızıl star1 300 Excel üçün yeni funksiyalar, Excel çox asan və güclü olun:

  • Veriyi ləğv etmədən Cell / Satır / Sütunları birləşdirin.
  • Bir neçə Çarşaf və İş Kitabını birləşdirin və birləşdirin.
  • Çaprazları müqayisə edin, birdən çox aralığa kopyalayın, mətni tarixə, vahidə və valyuta çevrilməsinə çevirin.
  • Rənglər, Paging Subtotals, Advanced Sort və Super Filter tərəfindən sayılan,
  • Daha çox seçin / Insert / Sil / Mətn / Biçim / Bağlantı / Yorum / Çalışma kitabları / Çalışma Qurğuları Tools ...

Excel üçün Kutools-un ekranı

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Ela · 1 months ago
    When I open the View Code window, there is a window but is not empty. Can I paste the code under the text that is already there or how do I open a new "blank page" please?
  • To post as a guest, your comment is unpublished.
    K Moen · 1 months ago
    I have windows for Mac , when I get to Step 4 - there is no option for Microsoft Scripting Runtime, is there something else I should be selecting?
  • To post as a guest, your comment is unpublished.
    Asfan Ayaz · 6 months ago
    this code is running on same sheet but how can I lookup cell color from one sheet to another sheet in excel
    Thanks in advance :)
    • To post as a guest, your comment is unpublished.
      Bob · 2 months ago
      Use this slight modification of the code posted.


      Public xDic As New Dictionary
      Public strWB As String
      Public strWS As String

      Function CLookup(ByRef FndValue, ByRef LookupRng As Range, ByRef xCol As Long)
      Dim xFindCell As Range
      On Error Resume Next

      strWB = LookupRng.Parent.Parent.Name '*** Remember the Workbook where the data and color are coming from
      strWS = LookupRng.Parent.Name '*** Remember the Worksheet where the data and color are coming from

      Set xFindCell = LookupRng.Find(FndValue, , xlValues, xlWhole)

      If xFindCell Is Nothing Then
      CLookup = ""
      xDic.Add Application.Caller.Address, ""
      Else
      CLookup = xFindCell.Offset(0, xCol - 1).Value
      xDic.Add Application.Caller.Address, xFindCell.Offset(0, xCol - 1).Address

      End If
      End Function

      Sub Worksheet_Change(ByVal Target As Range)
      Dim I As Long
      Dim xKeys As Long
      Dim xDicStr As String
      Dim rngLoc As Range
      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 = Application.Workbooks(strWB).Worksheets(strWS).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
  • To post as a guest, your comment is unpublished.
    Asfan · 6 months ago
    this code working on same sheet, how can i look up color from one sheet to another?
    • To post as a guest, your comment is unpublished.
      Bob · 2 months ago
      Use this slight modification of the code posted.


      Public xDic As New Dictionary
      Public strWB As String
      Public strWS As String

      Function CLookup(ByRef FndValue, ByRef LookupRng As Range, ByRef xCol As Long)
      Dim xFindCell As Range
      On Error Resume Next

      strWB = LookupRng.Parent.Parent.Name '*** Remember the Workbook where the data and color are coming from
      strWS = LookupRng.Parent.Name '*** Remember the Worksheet where the data and color are coming from

      Set xFindCell = LookupRng.Find(FndValue, , xlValues, xlWhole)

      If xFindCell Is Nothing Then
      CLookup = ""
      xDic.Add Application.Caller.Address, ""
      Else
      CLookup = xFindCell.Offset(0, xCol - 1).Value
      xDic.Add Application.Caller.Address, xFindCell.Offset(0, xCol - 1).Address

      End If
      End Function

      Sub Worksheet_Change(ByVal Target As Range)
      Dim I As Long
      Dim xKeys As Long
      Dim xDicStr As String
      Dim rngLoc As Range
      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 = Application.Workbooks(strWB).Worksheets(strWS).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
  • To post as a guest, your comment is unpublished.
    Brad Lehoux · 9 months ago
    I would also like to VLOOKUP on sheet 2 and extract the data and background color from sheet 1
    • To post as a guest, your comment is unpublished.
      Bob · 2 months ago
      Use this slight modification of the code posted.


      Public xDic As New Dictionary
      Public strWB As String
      Public strWS As String

      Function CLookup(ByRef FndValue, ByRef LookupRng As Range, ByRef xCol As Long)
      Dim xFindCell As Range
      On Error Resume Next

      strWB = LookupRng.Parent.Parent.Name '*** Remember the Workbook where the data and color are coming from
      strWS = LookupRng.Parent.Name '*** Remember the Worksheet where the data and color are coming from

      Set xFindCell = LookupRng.Find(FndValue, , xlValues, xlWhole)

      If xFindCell Is Nothing Then
      CLookup = ""
      xDic.Add Application.Caller.Address, ""
      Else
      CLookup = xFindCell.Offset(0, xCol - 1).Value
      xDic.Add Application.Caller.Address, xFindCell.Offset(0, xCol - 1).Address

      End If
      End Function

      Sub Worksheet_Change(ByVal Target As Range)
      Dim I As Long
      Dim xKeys As Long
      Dim xDicStr As String
      Dim rngLoc As Range
      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 = Application.Workbooks(strWB).Worksheets(strWS).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
      • To post as a guest, your comment is unpublished.
        Jennifer · 2 months ago
        It this to fix an error in the original code or is this to allow it to look up from a different sheet?
        • To post as a guest, your comment is unpublished.
          Bob · 2 months ago
          This change to the original code allows you to do the vlookup w/color from one Worksheet to another or from one Workbook to another. But this code needs to be placed in the TARGET worksheet rather than the SOURCE worksheet as was described in the original code. That's because the original code only worked in one Worksheet, so it was both the Source and the Target. This is not a fix to the original code. I just added code to allow you to pull from any Workbook/Worksheet (Source) into your Worksheet (Target). The original code worked as the programmer intended.