vba 多列查询条件格式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/33130885/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Multi-column vlookup conditional formatting
提问by mdaube
I'm looking to create a conditional format for a worksheet based on a couple of things.
我正在寻找基于几件事为工作表创建条件格式。
Sheet 1:
第 1 页：
Reference: "Date" = Col1 Row1
参考：“日期”= Col1 Row1
Date Census RN's On Schedule
7a 3p 11p 7a 3p 11p
1-Oct 8 8 9 8 8 9
2-Oct 10 14 13 13 16 13
3-Oct 10 12 12 12 12 10
4-Oct 12 10 10 11 11 10
5-Oct 10 11 11 11 12 11
6-Oct 10 11 14 11 12 12
7-Oct 13 12 12 12 14 12
Sheet 2:
第 2 页：
Reference: "Census" = Col1 Row1
参考：“人口普查”= Col1 Row1
Census RN Staffing Needs
7a 3p 11p
1 2 2 2
2 2 2 2
3 2 2 2
4 3 3 2
5 4 4 3
6 4.5 5 5
7 5 6 6
8 6 7 6.5
9 7 8 7
10 8.5 9 8.5
11 10 11 10
12 12 12 11.5
13 12 12 12
14 12 12 12
15 13 13 13
16 13 13 13
17 14 14 14
18 14 14 14
So here's the question.
那么问题来了。
I've got these values in sheet 1 that I'm looking to compare to sheet 2 and use conditional formatting to highlight certain things. I need excel to look at the census (for reference, 1-Oct census is 8 @ 7a) from sheet one and find it in sheet two. Then I need it to compare the number in RN's On Scheduleto the number in RN Staffing Needs(RN Staffing Needs, in this case 6 @ 7a; RN's On Schedule, 8). When the RN's on Schedule value from Sheet 1 (F3 in the case of 1-Oct) is HIGHER than the RN Staffing Needs from Sheet 2 I need a conditional format to highlight that value green. When it is LOWER I need it to highlight that value yellow. When there is an exact match nothing needs to happen.
我在工作表 1 中得到了这些值，我希望将它们与工作表 2 进行比较并使用条件格式来突出显示某些内容。我需要 excel 来查看第一页中的人口普查（供参考，1-Oct 人口普查是 8 @ 7a）并在第二页中找到它。然后我需要它来比较RN 的 On Schedule中的数字与RN Staffing Needs 中的数字（RN Staffing Needs，在本例中为 6 @ 7a；RN 的 On Schedule，为 8）。当表 1 中的 RN 值（在 1-Oct 的情况下为 F3）高于表 2 中的 RN 人员配备需求时，我需要一个条件格式来突出显示该值绿色。当它较低时，我需要它以黄色突出显示该值。当有完全匹配时，什么都不需要发生。
I've tried vlookups and index/matches with nested IF statements and IFERROR's and the like... this one's a little out of my scope of practice. Any help will be greatly appreciated!!
我已经尝试过使用嵌套 IF 语句和 IFERROR 之类的 vlookups 和索引/匹配......这个有点超出我的实践范围。任何帮助将不胜感激！！
采纳答案by Scott Holtzman
I took the approach below, but you can probably consolidate it even more.
我采用了下面的方法，但您可能可以进一步巩固它。
EDIT: I was able to consolidate it even more.
编辑：我能够更加巩固它。
Apply this formula for conditional formatting for the Range(E3:E9) in Sheet1. Then make an additional rule, but change the formula to >and to fill green. Then you can use the same two formats against columns F and G and you just need to change the column references in your formula and the vlookup column indicator.
将此公式应用于 Sheet1 中 Range(E3:E9) 的条件格式。然后制定一个额外的规则，但将公式更改为>和 以填充绿色。然后您可以对 F 和 G 列使用相同的两种格式，您只需要更改公式中的列引用和 vlookup 列指示符。
So, for column F, the formula would be =F3<vlookup(C3,Sheet2!$A:$D,3,0)
因此，对于 F 列，公式为 =F3<vlookup(C3,Sheet2!$A:$D,3,0)
LEAVING OLD METHOD IN HERE FOR MORE DETAILED UNDERSTANDING
将旧方法留在此处以获得更详细的理解
Sheet1
表 1
Enter the formula seen in the formula in cell I3 and drag through the end of the range (K9)
输入在单元格 I3 中的公式中看到的公式并拖动到范围的末尾 (K9)
Conditional Formatting
条件格式
Select the range from E3:G9 and apply the displayed formulas in the Conditional Formula Rules Manager.
从 E3:G9 中选择范围并应用条件公式规则管理器中显示的公式。
For reference my sheet2 looks like this (based on your data displayed).
作为参考，我的 sheet2 看起来像这样（基于您显示的数据）。
