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

如何在Excel中进行vlookup并返回背景色以及查找值?

假设您有一个表格,如下所示。 现在,您要检查指定的值是否在A列中,然后在C列中返回相应的值以及背景色。如何实现? 本文中的方法可以帮助您解决问题。

Vlookup并通过用户定义的函数返回具有查找值的背景色


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脚本运行时参考– VBAProject 对话框。 看截图:

5。 按 其他 + Q 退出键 Microsoft Visual Basic应用程序 窗口,然后返回工作表。

6.选择一个与查询值相邻的空白单元格,然后输入公式 =LookupKeepColor(E2,$A$1:$C$8,3) 进入公式栏,然后按Enter键。

备注: 在公式, E2 包含您将要查找的值, $ A $ 1:$ C $ 8 是表格范围和数字 3 表示您将返回的相应值位于表的第三列中。 请根据需要更改它们。

7.继续选择第一个结果单元格,然后向下拖动“填充手柄”以获取所有结果及其背景色。 查看截图。


相关文章:


最佳办公效率工具

Kutools for Excel解决了您的大多数问题,并使您的生产率提高了80%

  • 重用: 快速插入 复杂的公式,图表 以及您以前使用过的任何东西; 加密单元 带密码 创建邮件列表 并发送电子邮件...
  • 超级公式栏 (轻松编辑多行文本和公式); 阅读版式 (轻松读取和编辑大量单元格); 粘贴到过滤范围...
  • 合并单元格/行/列 不会丢失数据; 拆分单元格内容; 合并重复的行/列...防止细胞重复; 比较范围...
  • 选择重复或唯一 行; 选择空白行 (所有单元格都是空的); 超级查找和模糊查找 在许多工作簿中; 随机选择...
  • 确切的副本 多个单元格,无需更改公式参考; 自动创建参考 到多张纸; 插入项目符号,复选框等...
  • 提取文字,添加文本,按位置删除, 删除空间; 创建和打印分页小计; 在单元格内容和注释之间转换...
  • 超级滤镜 (将过滤方案保存并应用于其他工作表); 高级排序 按月/周/日,频率及更多; 特殊过滤器 用粗体,斜体...
  • 结合工作簿和工作表; 根据关键列合并表; 将数据分割成多个工作表; 批量转换xls,xlsx和PDF...
  • 超过300种强大功能. 支持 Office / Excel 2007-2021 和 365。支持所有语言。 在您的企业或组织中轻松部署。 完整功能 30 天免费试用。 60 天退款保证。
kte选项卡201905

Office选项卡为Office带来了选项卡式界面,使您的工作更加轻松

  • 在Word,Excel,PowerPoint中启用选项卡式编辑和阅读,发布者,Access,Visio和Project。
  • 在同一窗口的新选项卡中而不是在新窗口中打开并创建多个文档。
  • 每天将您的工作效率提高50%,并减少数百次鼠标单击!
officetab底部
按评论排序
注释 (34)
5中的5评分 · 1评级
该评论由网站上的主持人最小化
如何更改此代码,以便从另一张纸中提取背景颜色?
例如,我想在 Sheet 2 中使用 VLOOKUP,它从 Sheet 1 中提取数据和背景颜色。
该评论由网站上的主持人最小化
我有这个完全相同的问题! 任何建议将不胜感激。
该评论由网站上的主持人最小化
我还想在表 2 上进行 VLOOKUP 并从表 1 中提取数据和背景颜色
该评论由网站上的主持人最小化
使用发布的代码的这种轻微修改。


作为新词典的公共 xDic
公共 strWB 作为字符串
公共 strWS 作为字符串

函数 CLookup(ByRef FndValue, ByRef LookupRng As Range, ByRef xCol As Long)
将 xFindCell 调暗为范围
出错时继续下一步

strWB = LookupRng.Parent.Parent.Name '*** 记住数据和颜色来自的工作簿
strWS = LookupRng.Parent.Name '*** 记住数据和颜色来自的工作表

设置 xFindCell = LookupRng.Find(FndValue, , xlValues, xlWhole)

如果 xFindCell 什么都不是,那么
CLookup = ""
xDic.Add Application.Caller.Address, ""
其他
CLookup = xFindCell.Offset(0, xCol - 1).Value
xDic.Add Application.Caller.Address, xFindCell.Offset(0, xCol - 1).Address

结束如果
函数结束

Sub Worksheet_Change(ByVal Target As Range)
暗淡我只要
调暗 xKeys
将 xDicStr 调暗为字符串
Dim rngLoc 作为范围
出错时继续下一步
Application.ScreenUpdating = False
xKeys = UBound(xDic.Keys)
如果 xKeys >= 0 那么
对于 I = 0 到 UBound(xDic.Keys)
xDicStr = xDic.Items(I)
如果 xDicStr <> "" 那么
范围(xDic.Keys(I)).Interior.Color = Application.Workbooks(strWB).Worksheets(strWS).Range(xDic.Items(I)).Interior.Color
其他
范围(xDic.Keys(I))。Interior.Color = xlNone
结束如果
下一页
设置 xDic = 无
结束如果
Application.ScreenUpdating =真
END SUB
该评论由网站上的主持人最小化
这是为了修复原始代码中的错误,还是允许它从不同的工作表中查找?
该评论由网站上的主持人最小化
对原始代码的这种更改允许您使用颜色从一个工作表到另一个工作表或从一个工作簿到另一个工作表进行 vlookup。 但是此代码需要放置在 TARGET 工作表中,而不是原始代码中描述的 SOURCE 工作表中。 那是因为原始代码只在一个工作表中工作,所以它既是源代码又是目标代码。 这不是对原始代码的修复。 我刚刚添加了代码以允许您从任何工作簿/工作表(源)中提取到您的工作表(目标)中。 原始代码按照程序员的意图工作。
该评论由网站上的主持人最小化
你好,我做了这个程序,但我不能在新工作表中带上背景颜色,我怀疑我是否以正确的方式输入了命令 strWB 和 strWS 我把这个 strWB = LookupRng.Reporte_Opcionales
strWS = LookupRng.Imprimir Reporte_Opcionales 是我的工作簿的名称
该评论由网站上的主持人最小化
我相信这些行应该是以下(完全):

strWB = LookupRng.Parent.Parent.Name

strWS = LookupRng.Parent.Name


大约 4 个月前我想出了这个,所以我不记得我是如何想出这个的,但你不应该用其他任何东西替换这个代码。
该评论由网站上的主持人最小化
strWB 中的名称重复了 Parent.Parent ???? 对吗?
提前致谢。
该评论由网站上的主持人最小化
Bob,请帮帮我,请检查代码? 我确定你可以修复它,因为它确实会从其他工作表中获取背景颜色。

顺便说一句,用于在同一张工作表中工作的代码,但我需要从其他工作表中获取数据:(。

在此先感谢
来自墨西哥蒙特雷的问候语。
该评论由网站上的主持人最小化
这很好用,谢谢!
5中的5评分
该评论由网站上的主持人最小化
此代码在同一张纸上工作,我如何从一张纸查找颜色到另一张纸?
该评论由网站上的主持人最小化
使用发布的代码的这种轻微修改。


作为新词典的公共 xDic
公共 strWB 作为字符串
公共 strWS 作为字符串

函数 CLookup(ByRef FndValue, ByRef LookupRng As Range, ByRef xCol As Long)
将 xFindCell 调暗为范围
出错时继续下一步

strWB = LookupRng.Parent.Parent.Name '*** 记住数据和颜色来自的工作簿
strWS = LookupRng.Parent.Name '*** 记住数据和颜色来自的工作表

设置 xFindCell = LookupRng.Find(FndValue, , xlValues, xlWhole)

如果 xFindCell 什么都不是,那么
CLookup = ""
xDic.Add Application.Caller.Address, ""
其他
CLookup = xFindCell.Offset(0, xCol - 1).Value
xDic.Add Application.Caller.Address, xFindCell.Offset(0, xCol - 1).Address

结束如果
函数结束

Sub Worksheet_Change(ByVal Target As Range)
暗淡我只要
调暗 xKeys
将 xDicStr 调暗为字符串
Dim rngLoc 作为范围
出错时继续下一步
Application.ScreenUpdating = False
xKeys = UBound(xDic.Keys)
如果 xKeys >= 0 那么
对于 I = 0 到 UBound(xDic.Keys)
xDicStr = xDic.Items(I)
如果 xDicStr <> "" 那么
范围(xDic.Keys(I)).Interior.Color = Application.Workbooks(strWB).Worksheets(strWS).Range(xDic.Items(I)).Interior.Color
其他
范围(xDic.Keys(I))。Interior.Color = xlNone
结束如果
下一页
设置 xDic = 无
结束如果
Application.ScreenUpdating =真
END SUB
该评论由网站上的主持人最小化
你好鲍勃! 但是,该代码有效,由于某种原因,它将值从工作表 2 复制到工作表 1,但复制单元格格式并将其保留在工作表 2 中......这很难解释,但它基本上拆分了一个操作(复制文本 + 复制格式并将其粘贴到单元格中)一分为二。 你知道如何让它在一张纸上同时完成吗? 谢谢!
该评论由网站上的主持人最小化
此代码在同一张纸上运行,但我如何在 Excel 中从一张纸查找单元格颜色到另一张纸
在此先感谢:)
该评论由网站上的主持人最小化
使用发布的代码的这种轻微修改。


作为新词典的公共 xDic
公共 strWB 作为字符串
公共 strWS 作为字符串

函数 CLookup(ByRef FndValue, ByRef LookupRng As Range, ByRef xCol As Long)
将 xFindCell 调暗为范围
出错时继续下一步

strWB = LookupRng.Parent.Parent.Name '*** 记住数据和颜色来自的工作簿
strWS = LookupRng.Parent.Name '*** 记住数据和颜色来自的工作表

设置 xFindCell = LookupRng.Find(FndValue, , xlValues, xlWhole)

如果 xFindCell 什么都不是,那么
CLookup = ""
xDic.Add Application.Caller.Address, ""
其他
CLookup = xFindCell.Offset(0, xCol - 1).Value
xDic.Add Application.Caller.Address, xFindCell.Offset(0, xCol - 1).Address

结束如果
函数结束

Sub Worksheet_Change(ByVal Target As Range)
暗淡我只要
调暗 xKeys
将 xDicStr 调暗为字符串
Dim rngLoc 作为范围
出错时继续下一步
Application.ScreenUpdating = False
xKeys = UBound(xDic.Keys)
如果 xKeys >= 0 那么
对于 I = 0 到 UBound(xDic.Keys)
xDicStr = xDic.Items(I)
如果 xDicStr <> "" 那么
范围(xDic.Keys(I)).Interior.Color = Application.Workbooks(strWB).Worksheets(strWS).Range(xDic.Items(I)).Interior.Color
其他
范围(xDic.Keys(I))。Interior.Color = xlNone
结束如果
下一页
设置 xDic = 无
结束如果
Application.ScreenUpdating =真
END SUB
该评论由网站上的主持人最小化
我有 Mac 的 windows ,当我进入第 4 步时 - 没有 Microsoft Scripting Runtime 选项,我还应该选择其他什么吗?
该评论由网站上的主持人最小化
当我打开查看代码窗口时,有一个窗口但不是空的。 我可以将代码粘贴在已经存在的文本下吗?或者如何打开一个新的“空白页”?
该评论由网站上的主持人最小化
我正在返回一个值,但没有得到颜色。 使用工作表到工作表代码,然后是 T。关于为什么我没有得到颜色的任何想法?
该评论由网站上的主持人最小化
有没有办法修改它以用作 Hlookup?
该评论由网站上的主持人最小化
下午好,鲍勃这些代码除了颜色之外,您还可以更改它们给我打电话给包含单元格的相同颜色格式和字体

谢谢
该评论由网站上的主持人最小化
这在 Office 2010 中运行良好,但在 2013 版本中运行良好。 宏有更新吗?
该评论由网站上的主持人最小化
嗨,我可以在其中没有数据的颜色单元格上应用 vlookup
该评论由网站上的主持人最小化
我得到了所需的单元格颜色,但我还需要查找值,因为它返回整数而不是字符串
该评论由网站上的主持人最小化
我在 Excel 2016 中使用过这个,只有数据从源传输到目标......。颜色没有传输。 关于可能是什么问题的想法:它是否与 Excel 2016 不兼容? 谢谢。 公吨
该评论由网站上的主持人最小化
这太棒了! 按照步骤操作,效果很好! 谢谢!
该评论由网站上的主持人最小化
我有很多记录,处理时间太长,完成后代码仍在运行。 请帮忙
该评论由网站上的主持人最小化
您好,我有一张 10,948 行的表格,它需要一些时间来提取颜色信息,仍在等待。 这是正常的,还是有什么问题?
该评论由网站上的主持人最小化
我怎样做
该评论由网站上的主持人最小化
我使用 Excel 报告中的时间和日期为我们的员工创建时间表。 如果指定的日期,例如 2020/08/11 与下一个选项卡数组(包含许多具有相同日期但不同时间的单元格)上的日期匹配,我希望它只拉出填充为橙色的单元格,这将被声明为2020/08/11 7:45。 这可能吗?
该评论由网站上的主持人最小化
嗨,此代码适用于 office 2016 及更高版本吗?
该评论由网站上的主持人最小化
不,它不返回颜色。
该评论由网站上的主持人最小化
这段代码工作正常,除了在输入公式的单元格中,当它查找的单元格为空白时显示 0 ,我的问题是如何让它忽略空白单元格并防止公式所在的单元格输入a 0,代码中是否有一些地方可以输入 =IFERROR 函数?
该评论由网站上的主持人最小化
嗨凯尔,

我测试了这段代码,当它查找的单元格为空白时,它不会显示 0。
也许您可以将公式包含在 IF 函数中,如下所示,以防止返回结果 0。
=IF(B2="","",LookupKeepColor(E2,$A$1:$C$8,3))
这里还没有评论
留下你的意见
以访客身份发帖
×
评价此帖子:
0   产品特性
建议地点

关注我们

版权所有 © 2009 - extendoffice.com。 | 版权所有。 供电 ExtendOffice。 | 网站地图
Microsoft和Office徽标是Microsoft Corporation在美国和/或其他国家的商标或注册商标。
受Sectigo SSL保护