Note: The other languages of the website are Google-translated. Back to English

Làm cách nào để vlookup và trả về màu nền cùng với giá trị tra cứu trong Excel?

Giả sử bạn có một bảng như hình minh họa bên dưới. Bây giờ bạn muốn kiểm tra xem một giá trị được chỉ định có nằm trong cột A hay không và sau đó trả về giá trị tương ứng cùng với màu nền trong cột C. Làm thế nào để đạt được nó? Phương pháp trong bài có thể giúp bạn giải quyết vấn đề.

Vlookup và trả về màu nền với giá trị tra cứu bằng chức năng Người dùng xác định


Vlookup và trả về màu nền với giá trị tra cứu bằng chức năng Người dùng xác định

Vui lòng làm như sau để tra cứu một giá trị và trả về giá trị tương ứng của nó cùng với màu nền trong Excel.

1. Trong trang tính có chứa giá trị bạn muốn vlookup, nhấp chuột phải vào tab trang tính và chọn Mã Chế độ xem từ menu ngữ cảnh. Xem ảnh chụp màn hình:

2. Trong phần mở đầu Microsoft Visual Basic cho các ứng dụng , vui lòng sao chép mã VBA bên dưới vào cửa sổ Mã.

Mã VBA 1: Vlookup và trả về màu nền với giá trị tra cứu

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. Sau đó nhấn vào Chèn > Mô-đunvà sao chép mã VBA 2 bên dưới vào cửa sổ Mô-đun.

Mã VBA 2: Vlookup và trả về màu nền với giá trị tra cứu

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. Sau khi chèn hai mã, hãy nhấp vào CÔNG CỤ > dự án. Sau đó kiểm tra Thời gian chạy Microsoft Script hộp trong Tài liệu tham khảo - VBAProject hộp thoại. Xem ảnh chụp màn hình:

5. Nhấn nút Khác + Q chìa khóa để thoát khỏi Microsoft Visual Basic cho các ứng dụng cửa sổ và quay lại trang tính.

6. Chọn một ô trống bên cạnh giá trị tra cứu, sau đó nhập công thức =LookupKeepColor(E2,$A$1:$C$8,3) vào Thanh công thức, rồi nhấn phím Enter.

Chú thích: Trong công thức, E2 chứa giá trị bạn sẽ tra cứu, $ A $ 1: $ C $ 8 là phạm vi bảng và số 3 nghĩa là giá trị tương ứng bạn sẽ trả về nằm trong cột thứ ba của bảng. Vui lòng thay đổi chúng khi bạn cần.

7. Tiếp tục chọn ô kết quả đầu tiên và kéo Fill Handle xuống để nhận tất cả các kết quả cùng với màu nền của chúng. Xem ảnh chụp màn hình.


Các bài liên quan:


Các công cụ năng suất văn phòng tốt nhất

Kutools cho Excel giải quyết hầu hết các vấn đề của bạn và tăng 80% năng suất của bạn

  • Tái sử dụng: Chèn nhanh công thức phức tạp, biểu đồ và bất cứ thứ gì bạn đã sử dụng trước đây; Mã hóa ô với mật khẩu; Tạo danh sách gửi thư và gửi email ...
  • Thanh siêu công thức (dễ dàng chỉnh sửa nhiều dòng văn bản và công thức); Bố cục đọc (dễ dàng đọc và chỉnh sửa số lượng ô lớn); Dán vào Dải ô đã Lọchữu ích. Cảm ơn !
  • Hợp nhất các ô / hàng / cột mà không làm mất dữ liệu; Nội dung phân chia ô; Kết hợp các hàng / cột trùng lặp... Ngăn chặn các ô trùng lặp; So sánh các dãyhữu ích. Cảm ơn !
  • Chọn trùng lặp hoặc duy nhất Hàng; Chọn hàng trống (tất cả các ô đều trống); Tìm siêu và Tìm mờ trong Nhiều Sổ làm việc; Chọn ngẫu nhiên ...
  • Bản sao chính xác Nhiều ô mà không thay đổi tham chiếu công thức; Tự động tạo tài liệu tham khảo sang Nhiều Trang tính; Chèn Bullets, Hộp kiểm và hơn thế nữa ...
  • Trích xuất văn bản, Thêm Văn bản, Xóa theo Vị trí, Xóa không gian; Tạo và In Tổng số phân trang; Chuyển đổi giữa nội dung ô và nhận xéthữu ích. Cảm ơn !
  • Siêu lọc (lưu và áp dụng các lược đồ lọc cho các trang tính khác); Sắp xếp nâng cao theo tháng / tuần / ngày, tần suất và hơn thế nữa; Bộ lọc đặc biệt bằng cách in đậm, in nghiêng ...
  • Kết hợp Workbook và WorkSheets; Hợp nhất các bảng dựa trên các cột chính; Chia dữ liệu thành nhiều trang tính; Chuyển đổi hàng loạt xls, xlsx và PDFhữu ích. Cảm ơn !
  • Hơn 300 tính năng mạnh mẽ. Hỗ trợ Office / Excel 2007-2021 và 365. Hỗ trợ tất cả các ngôn ngữ. Dễ dàng triển khai trong doanh nghiệp hoặc tổ chức của bạn. Đầy đủ tính năng Dùng thử miễn phí 30 ngày. Bảo đảm hoàn lại tiền trong 60 ngày.
tab kte 201905

Tab Office mang lại giao diện Tab cho Office và giúp công việc của bạn trở nên dễ dàng hơn nhiều

  • Cho phép chỉnh sửa và đọc theo thẻ trong Word, Excel, PowerPoint, Publisher, Access, Visio và Project.
  • Mở và tạo nhiều tài liệu trong các tab mới của cùng một cửa sổ, thay vì trong các cửa sổ mới.
  • Tăng 50% năng suất của bạn và giảm hàng trăm cú nhấp chuột cho bạn mỗi ngày!
officetab dưới cùng
Nhận xét (34)
Xếp hạng 5 trong 5 · xếp hạng 1
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Làm cách nào để thay đổi mã này, để nó lấy màu nền từ một trang tính khác?
Ví dụ: tôi muốn sử dụng hàm VLOOKUP trong Trang tính 2, nó trích xuất dữ liệu và màu nền từ Trang tính 1.
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Tôi có cùng một câu hỏi này! Bất kỳ lời khuyên sẽ được đánh giá rất cao.
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Tôi cũng muốn VLOOKUP trên trang tính 2 và trích xuất dữ liệu và màu nền từ trang tính 1
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Sử dụng sửa đổi nhỏ này của mã đã đăng.


Công xDic Như Từ Điển Mới
StrWB công khai dưới dạng chuỗi
StrWS công khai dưới dạng chuỗi

Hàm CLookup (ByRef FndValue, ByRef LookupRng As Range, ByRef xCol As Long)
Làm mờ xFindCell theo phạm vi
On Error Resume Next

strWB = LookupRng.Parent.Parent.Name '*** Hãy nhớ Workbook nơi chứa dữ liệu và màu sắc
strWS = LookupRng.Parent.Name '*** Hãy nhớ Bảng tính nơi lấy dữ liệu và màu sắc

Đặt xFindCell = LookupRng.Find (FndValue,, xlValues, xlWhole)

Nếu xFindCell không có gì thì
CLookup = ""
xDic.Add Application.Caller.Address, ""
Khác
CLookup = xFindCell.Offset (0, xCol - 1) .Value
xDic.Add Application.Caller.Address, xFindCell.Offset (0, xCol - 1) .Address

Cuối Nếu
Cuối Chức năng

Sub Worksheet_Change (ByVal Target As Range)
Dim I As Long
Dim xKeys càng lâu càng tốt
Làm mờ xDicStr dưới dạng chuỗi
Dim rngLoc As Range
On Error Resume Next
Application.ScreenUpdating = Sai
xKeys = UBound (xDic.Keys)
Nếu xKeys> = 0 Thì
Đối với I = 0 Đến UBound (xDic.Keys)
xDicStr = xDic.Items (I)
Nếu xDicStr <> "" Thì
Phạm vi (xDic.Keys (I)). Interior.Color = Application.Workbooks (strWB) .Worksheets (strWS) .Range (xDic.Items (I)). Interior.Color
Khác
Phạm vi (xDic.Keys (I)). Interior.Color = xlNone
Cuối Nếu
Sau
Đặt xDic = Không có gì
Cuối Nếu
Application.ScreenUpdating = True
End Sub
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Đây là cách để sửa lỗi trong mã gốc hay là để cho phép nó tra cứu từ một trang tính khác?
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Thay đổi này đối với mã gốc cho phép bạn thực hiện vlookup w / color từ Trang tính này sang Trang tính khác hoặc từ Sổ làm việc này sang Sổ làm việc khác. Nhưng mã này cần được đặt trong trang tính TARGET chứ không phải trang tính NGUỒN như được mô tả trong mã gốc. Đó là bởi vì mã gốc chỉ hoạt động trong một Trang tính, vì vậy nó vừa là Nguồn vừa là Mục tiêu. Đây không phải là bản sửa lỗi cho mã gốc. Tôi vừa thêm mã để cho phép bạn kéo từ bất kỳ Sổ làm việc / Trang tính nào (Nguồn) vào Trang tính (Mục tiêu) của bạn. Mã ban đầu hoạt động như ý định của lập trình viên.
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
xin chào, tôi đã làm đúng thủ tục nhưng tôi không thể mang màu nền trong trang tính mới, tôi có nghi ngờ nếu tôi đặt đúng cách comand strWB và strWS tôi đã đặt strWB = LookupRng.Reporte_Opcionales này
strWS = LookupRng.Imprimir Reporte_Opcionales là tên sổ làm việc của tôi
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Tôi tin rằng các dòng được cho là như sau (CHÍNH XÁC):

strWB = LookupRng.Parent.Parent.Name

strWS = LookupRng.Parent.Name


Tôi đã nghĩ ra điều này khoảng 4 tháng trước vì vậy tôi không nhớ chính xác làm thế nào tôi nghĩ ra điều này, nhưng bạn không nên thay thế mã này bằng bất kỳ thứ gì khác.
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
tên trong strWB đã lặp lại Parent.Parent là gì ???? điều đó có chính xác không?
cảm ơn trước.
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Bob, làm ơn giúp tôi, xin vui lòng kiểm tra mã? Tôi chắc chắn rằng bạn có thể sửa nó vì nó làm lu mờ màu nền từ trang tính khác.

nhân tiện, mã dành cho công việc trong cùng một trang tính hoạt động nhưng tôi cần mang dữ liệu từ trang tính khác :(.

cảm ơn trước
lời chào từ Monterrey México.
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Điều này làm việc tuyệt vời, cảm ơn bạn!
Xếp hạng 5 trong 5
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
mã này hoạt động trên cùng một trang tính, làm thế nào tôi có thể tra cứu màu từ trang này sang trang khác?
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Sử dụng sửa đổi nhỏ này của mã đã đăng.


Công xDic Như Từ Điển Mới
StrWB công khai dưới dạng chuỗi
StrWS công khai dưới dạng chuỗi

Hàm CLookup (ByRef FndValue, ByRef LookupRng As Range, ByRef xCol As Long)
Làm mờ xFindCell theo phạm vi
On Error Resume Next

strWB = LookupRng.Parent.Parent.Name '*** Hãy nhớ Workbook nơi chứa dữ liệu và màu sắc
strWS = LookupRng.Parent.Name '*** Hãy nhớ Bảng tính nơi lấy dữ liệu và màu sắc

Đặt xFindCell = LookupRng.Find (FndValue,, xlValues, xlWhole)

Nếu xFindCell không có gì thì
CLookup = ""
xDic.Add Application.Caller.Address, ""
Khác
CLookup = xFindCell.Offset (0, xCol - 1) .Value
xDic.Add Application.Caller.Address, xFindCell.Offset (0, xCol - 1) .Address

Cuối Nếu
Cuối Chức năng

Sub Worksheet_Change (ByVal Target As Range)
Dim I As Long
Dim xKeys càng lâu càng tốt
Làm mờ xDicStr dưới dạng chuỗi
Dim rngLoc As Range
On Error Resume Next
Application.ScreenUpdating = Sai
xKeys = UBound (xDic.Keys)
Nếu xKeys> = 0 Thì
Đối với I = 0 Đến UBound (xDic.Keys)
xDicStr = xDic.Items (I)
Nếu xDicStr <> "" Thì
Phạm vi (xDic.Keys (I)). Interior.Color = Application.Workbooks (strWB) .Worksheets (strWS) .Range (xDic.Items (I)). Interior.Color
Khác
Phạm vi (xDic.Keys (I)). Interior.Color = xlNone
Cuối Nếu
Sau
Đặt xDic = Không có gì
Cuối Nếu
Application.ScreenUpdating = True
End Sub
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Xin chào Bob! Tuy nhiên, mã hoạt động vì một lý do nào đó, nó sao chép các giá trị từ Trang tính 2 sang Trang tính 1, nhưng sao chép định dạng ô và để nó trong Trang tính 2 ... Thật khó giải thích, nhưng về cơ bản nó phân chia một hành động (sao chép văn bản + sao chép hình thành và dán nó vào ô) thành hai. Bạn có biết làm thế nào để làm cho nó để làm cả hai trên một trang tính? Cảm ơn bạn!
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
mã này đang chạy trên cùng một trang tính nhưng làm cách nào để tôi có thể tra cứu màu ô từ trang tính này sang trang tính khác trong excel
Cảm ơn trước :)
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Sử dụng sửa đổi nhỏ này của mã đã đăng.


Công xDic Như Từ Điển Mới
StrWB công khai dưới dạng chuỗi
StrWS công khai dưới dạng chuỗi

Hàm CLookup (ByRef FndValue, ByRef LookupRng As Range, ByRef xCol As Long)
Làm mờ xFindCell theo phạm vi
On Error Resume Next

strWB = LookupRng.Parent.Parent.Name '*** Hãy nhớ Workbook nơi chứa dữ liệu và màu sắc
strWS = LookupRng.Parent.Name '*** Hãy nhớ Bảng tính nơi chứa dữ liệu và màu sắc

Đặt xFindCell = LookupRng.Find (FndValue,, xlValues, xlWhole)

Nếu xFindCell không có gì thì
CLookup = ""
xDic.Add Application.Caller.Address, ""
Khác
CLookup = xFindCell.Offset (0, xCol - 1) .Value
xDic.Add Application.Caller.Address, xFindCell.Offset (0, xCol - 1) .Address

Cuối Nếu
Cuối Chức năng

Sub Worksheet_Change (ByVal Target As Range)
Dim I As Long
Dim xKeys càng lâu càng tốt
Làm mờ xDicStr dưới dạng chuỗi
Dim rngLoc As Range
On Error Resume Next
Application.ScreenUpdating = Sai
xKeys = UBound (xDic.Keys)
Nếu xKeys> = 0 Thì
Đối với I = 0 Đến UBound (xDic.Keys)
xDicStr = xDic.Items (I)
Nếu xDicStr <> "" Thì
Phạm vi (xDic.Keys (I)). Interior.Color = Application.Workbooks (strWB) .Worksheets (strWS) .Range (xDic.Items (I)). Interior.Color
Khác
Phạm vi (xDic.Keys (I)). Interior.Color = xlNone
Cuối Nếu
Sau
Đặt xDic = Không có gì
Cuối Nếu
Application.ScreenUpdating = True
End Sub
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Tôi có cửa sổ cho Mac, khi tôi chuyển đến Bước 4 - không có tùy chọn nào cho Microsoft Scripting Runtime, có điều gì khác mà tôi nên chọn không?
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Khi tôi mở cửa sổ Mã Chế độ xem, có một cửa sổ nhưng không trống. Tôi có thể dán mã bên dưới văn bản đã có hay làm cách nào để mở một "trang trống" mới?
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Tôi đang trả về một giá trị, nhưng không nhận được màu sắc. đã sử dụng trang tính này sang mã trang tính, tiếp theo là chữ T. Có bất kỳ ý kiến ​​nào về lý do tại sao tôi không nhận được màu sắc không?
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Có cách nào để sửa đổi điều này để sử dụng làm Hlookup không?
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
chào buổi chiều bob với những mã này, bạn có thể thay đổi chúng ngoài màu sắc, hãy gọi cho tôi cùng một định dạng màu và phông chữ có chứa ô

Cảm ơn bạn
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Điều này hoạt động tốt trong Office 2010, nhưng không hoạt động với phiên bản 2013. Có bản cập nhật cho macro không?
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Xin chào, Tôi có thể áp dụng vlookup trên các ô màu không có dữ liệu trong đó không
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
tôi đang nhận được màu ô cần thiết nhưng tôi cũng cần giá trị tra cứu vì nó đang trả về số nguyên thay vì chuỗi
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Tôi đã sử dụng điều này trong Excel 2016 và chỉ dữ liệu được chuyển từ Nguồn sang Mục tiêu ...… .color không được chuyển. Suy nghĩ về vấn đề có thể là: Có phải nó không tương thích với Excel 2016 không? Cảm ơn. MT
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Nó thật là tuyệt vời! đã làm theo các bước và nó hoạt động tuyệt vời! Cảm ơn bạn!
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Tôi có nhiều bản ghi, mất quá nhiều thời gian để xử lý và mã vẫn tiếp tục chạy ngay cả sau khi hoàn thành. Xin vui lòng giúp đỡ
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Xin chào, tôi có một trang tính với 10,948 hàng, mất một chút thời gian để lấy thông tin với màu sắc, vẫn đang chờ. Điều này có bình thường không, hay có gì đó không ổn?
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Tôi lam thê nao
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Tôi sử dụng thời gian và ngày tháng từ các báo cáo excel để tạo bảng chấm công cho nhân viên của chúng tôi. Nếu ngày được chỉ định, ví dụ: 2020/08/11 khớp với ngày trên mảng tab tiếp theo (chứa nhiều ô có cùng ngày nhưng khác thời gian), tôi muốn nó chỉ kéo ô được tô màu cam sẽ được nêu là 2020/08/11 7:45. Điều này có khả thi không?
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Xin chào, Mã này có hoạt động cho các phiên bản Office 2016 và mới hơn không?
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
không nó không trở lại màu sắc.
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Mã này đang hoạt động tốt, ngoại trừ trong các ô mà công thức được nhập vào trả về 0 khi ô mà nó đang tra cứu trống, câu hỏi của tôi là làm cách nào để làm cho nó bỏ qua các ô trống và ngăn không cho ô nhập công thức vào a 0 , có thể có một số vị trí trong mã để nhập hàm =IFERROR không?
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Chào Kyle,

Tôi đã kiểm tra mã này và nó không hiển thị 0 khi ô mà nó tìm kiếm trống.
Có lẽ bạn có thể đưa công thức vào hàm IF, như minh họa bên dưới, để tránh trả về kết quả là 0.
=IF(B2="","",LookupKeepColor(E2,$A$1:$C$8,3))
Không có bình luận nào được đăng ở đây
Để lại ý kiến ​​của bạn
Đăng với tư cách khách
×
Đánh giá bài viết này:
0   Nhân vật
Các vị trí được đề xuất

Kết nối với chúng tôi

Bản quyền © 2009 - www.extendoffice.com. | Đã đăng ký Bản quyền. cung cấp bởi ExtendOffice. | BẢN ĐỒ CHI NHÁNH
Microsoft và logo Office là các nhãn hiệu hoặc nhãn hiệu đã đăng ký của Microsoft Corporation tại Hoa Kỳ và / hoặc các quốc gia khác.
Được bảo vệ bởi Sectigo SSL