팁 : 다른 언어는 Google 번역입니다. 방문하실 수 있습니다. English 이 링크의 버전.
로그인
x
or
x
x
회원가입
x

or

vlookup하고 Excel에서 조회 값과 함께 배경색을 반환하는 방법?

아래 스크린 샷과 같은 표가 있다고 가정합니다. 이제 지정된 값이 A 열에 있는지 확인한 다음 C 열의 배경색과 함께 해당 값을 반환합니다. 어떻게 수행합니까? 이 기사의 방법은 문제를 해결하는 데 도움을 줄 수 있습니다.

Vlookup 및 사용자 정의 함수에 의한 조회 값을 가진 배경색 반환

Office 탭 Office에서 탭 편집 및 찾아보기를 사용하고 훨씬 쉽게 작업 할 수 있습니다 ...
Excel 용 Kutools는 대부분의 문제를 해결하고 생산성을 80 % 증가시킵니다.
  • 무엇이든 재사용하십시오 : 가장 많이 사용되거나 복잡한 수식, 차트 및 기타 항목을 즐겨 찾기에 추가하고 나중에 빠르게 재사용하십시오.
  • 20 이상의 텍스트 기능 : 텍스트 문자열에서 숫자 추출; 텍스트 일부 추출 또는 제거; 숫자와 통화를 영어 단어로 변환합니다.
  • 병합 도구: 여러 통합 문서 및 시트를 하나로 통합합니다. 데이터 손실없이 여러 셀 / 행 / 열 병합 중복 행 병합 및 합계
  • 분할 도구: 값을 기준으로 데이터를 여러 시트로 분할; 하나의 통합 문서에서 여러 Excel, PDF 또는 CSV 파일로; 하나의 열에서 여러 열로.
  • 건너 뛰기 붙여 넣기 숨겨진 / 필터링 된 행; 수와 합계 배경색 별; 여러 전자 메일 수신자에게 개인화 된 전자 메일 보내기
  • 수퍼 필터 : 고급 필터 구성표를 작성하고 모든 시트에 적용하십시오. 종류 주별, 일별, 빈도 등으로; FILTER 굵게, 수식, 주석으로 ...
  • 300 이상의 강력한 기능; Office 2007-2019 및 365와 호환됩니다. 모든 언어를 지원합니다. 기업이나 조직에 쉽게 배포 할 수 있습니다.

Vlookup 및 사용자 정의 함수에 의한 조회 값을 가진 배경색 반환


값을 조회하고 해당 값을 Excel에서 배경색과 함께 반환하려면 다음을 수행하십시오.

1. 워크 시트에 vlookup 할 값이 포함되어 있으면 시트 탭을 마우스 오른쪽 버튼으로 클릭하고 코드보기 컨텍스트 메뉴에서. 스크린 샷보기 :

2. 오프닝에서 응용 프로그램 용 Microsoft Visual Basic 윈도우에서 VBA 코드를 코드 창에 복사하십시오.

VBA 코드 1 : 조회 값이있는 Vlookup 및 배경 색상 반환

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 : 조회 값이있는 Vlookup 및 배경 색상 반환

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 상자에 참조 - VBAProject 대화 상자. 스크린 샷보기 :

5. 누르세요 다른 + Q 종료 키 응용 프로그램 용 Microsoft Visual Basic 창을 열고 워크 시트로 돌아갑니다.

6. 조회 값에 인접한 빈 셀을 선택한 다음 수식을 입력하십시오. =LookupKeepColor(E2,$A$1:$C$8,3) 수식 입력 줄에 입력 한 다음 Enter 키를 누릅니다.

주의 사항: 공식에서, E2 조회 할 값을 포함하며, $ A $ 1 : $ C $ 8 테이블 범위 및 숫자입니다. 3 반환 할 해당 값이 테이블의 세 번째 열에 있음을 의미합니다. 필요에 따라 변경하십시오.

7. 첫 번째 결과 셀을 계속 선택하고 채우기 핸들을 끌어 배경색과 함께 모든 결과를 얻습니다. 스크린 샷을 참조하십시오.


관련 기사 :


Excel 용 Kutools는 대부분의 문제를 해결하고 생산성을 80 % 증가시킵니다.

  • 재사용: 빠르게 삽입 복잡한 수식, 차트 그리고 당신이 전에 사용했던 것; 셀 암호화 비밀번호로; 메일 링리스트 만들기 그리고 이메일을 보내 ...
  • 슈퍼 포뮬러 바 (여러 줄의 텍스트와 수식을 쉽게 편집); 레이아웃 읽기 (많은 셀을 쉽게 읽고 편집); 필터링 된 범위에 붙여 넣기...
  • 셀 / 행 / 열 병합 데이터 손실없이; 분할 셀 내용; 중복 행 / 열 결합... 중복 세포 방지; 범위 비교...
  • 복제 또는 고유를 선택하십시오. 행; 빈 행 선택 (모든 세포는 비어있다); 슈퍼 찾기 및 퍼지 찾기 많은 통합 문서에서; 랜덤 선택 ...
  • 정확한 사본 공식 참조를 변경하지 않는 다중 셀; 참조 자동 작성 여러 장에; 글 머리 기호 삽입, 확인란 등 ...
  • 텍스트 추출, 텍스트 추가, 위치 별 제거, 공간 제거; 페이징 소계 생성 및 인쇄; 셀 내용과 주석 간 변환...
  • 수퍼 필터 (필터 구성표를 저장하고 다른 시트에 적용); 고급 정렬 월 / 주 / 일별, 빈도 등; 특수 필터 대담하고 기울임 꼴로
  • 통합 문서와 WorkSheets 결합; 키 열을 기준으로 테이블 병합 데이터를 여러 시트로 분할; 일괄 변환 xls, xlsx 및 PDF...
  • 300 이상의 강력한 기능. Office / Excel 2007-2019 및 365를 지원합니다. 모든 언어를 지원합니다. 기업이나 조직에 쉽게 배포 할 수 있습니다. 30 일 무료 평가판 전체 기능.
kte 탭 201905

Office 탭 Office에 탭 인터페이스를 제공하여 작업을 훨씬 쉽게

  • Word, Excel, PowerPoint에서 탭 편집 및 읽기 사용, 게시자, 액세스, Visio 및 프로젝트.
  • 새 창보다는 동일한 창에서 새 탭으로 여러 문서를 열고 만들 수 있습니다.
  • 50 %만큼 생산성을 높이고 매일 수백 번의 마우스 클릭을 줄입니다!
오피셜
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.
    Prashant · 8 days ago
    Hi, Can i apply vlookup on color cells with no data in them
  • To post as a guest, your comment is unpublished.
    Richard S · 19 days ago
    this works fine in office 2010, but not the 2013 version. Is there an update to the macro?
  • To post as a guest, your comment is unpublished.
    Jhon Rojas · 2 months ago
    good afternoon bob to these codes you can change them in addition to the color call me the same color format and font that contains the cell

    Thank you
  • To post as a guest, your comment is unpublished.
    Ari · 4 months ago
    Is there any way to modify this to use as an Hlookup?
  • To post as a guest, your comment is unpublished.
    Jonathan · 5 months ago
    I am returning a value, but not getting the color. used the sheet to sheet code, followed to a T. Any ideas on why I am not getting the color?
  • To post as a guest, your comment is unpublished.
    Ela · 8 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 · 9 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 · 1 years 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 · 10 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 · 1 years 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 · 10 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.
        Ana · 3 months ago
        Hello Bob! The code works, however, for some reason it copies values from Sheet 2 to Sheet 1, but copies the cell formatting and leaves it in Sheet 2... It's hard to explain, but it basically splits one action (copy text + copy formation and paste it into the cell) into two. Do you know how to make it to do both on one sheet? Thank you!
  • To post as a guest, your comment is unpublished.
    Brad Lehoux · 1 years 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 · 10 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 · 10 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 · 10 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.
          • To post as a guest, your comment is unpublished.
            alan · 5 months ago
            hello I didi it the procedure but i cant bring the background color in the new worksheet , i have a doubt if i put in correct way the comand strWB and strWS i puted this strWB = LookupRng.Reporte_Opcionales
            strWS = LookupRng.Imprimir Reporte_Opcionales is the name of my workbook
            • To post as a guest, your comment is unpublished.
              Bob · 5 months ago
              I believe the lines are supposed to be the following (EXACTLY):

              strWB = LookupRng.Parent.Parent.Name

              strWS = LookupRng.Parent.Name


              I came up with this about 4 months ago so I don't remember exactly how I came up with this, but you weren't supposed to replace this code with anything else.
              • To post as a guest, your comment is unpublished.
                Sergio Salazar Brice · 4 months ago
                Bob, help me plese , colud you please check the code? im sure that you can fix it because it does brigme the background color from other sheet .

                by the way the code that is for work in the same sheet work but i need bring the data from other sheet :(.

                thanks in advance
                greetengs from Monterrey México.
              • To post as a guest, your comment is unpublished.
                alansystem@hotmail.c · 4 months ago
                what the name in strWB has repeated Parent.Parent ???? does that correct?
                thanks in advance.
  • To post as a guest, your comment is unpublished.
    Sander Viet · 1 years ago
    How do I change this code, in order for it to extract the background color from another sheet?
    For example, I would like to use a VLOOKUP in Sheet 2, which extracts the data and background color from Sheet 1.
    • To post as a guest, your comment is unpublished.
      Jim · 1 years ago
      I have this exact same question! Any advice would be greatly appreciated.