vba 使单元格颜色与另一个单元格颜色相同并保留公式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26714147/
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
Make cell color the same as another cell color and keep formula
提问by Lotus
In Excel 2007, I've conditional formatting for column Q:
在 Excel 2007 中，我为 Q 列设置了条件格式：
- values > -5 = green
- values between -5 and -25 are yellow and
- values less than -25 are red.
- 值 > -5 = 绿色
- -5 到 -25 之间的值是黄色和
- 小于 -25 的值是红色的。
I want cell P5 to have the same color as Q5, Cell P6 to have the same color as Q6 and so forth.
我希望单元格 P5 具有与 Q5 相同的颜色，单元格 P6 具有与 Q6 相同的颜色等等。
The value in cell P5 is dependent on a formula (K5-C5),
the value in cell P6 is dependent on a formula (K6-C6) and so forth.
单元格 P5 中的值取决于公式 (K5-C5)，
单元格 P6 中的值取决于公式 (K6-C6)，依此类推。
How can I change the color of the cells in column P so they are equal to their "matching" cells in column Q, without ruining the formulas created in column P?
如何更改 P 列中单元格的颜色，使其与 Q 列中的“匹配”单元格相同，而不会破坏 P 列中创建的公式？
回答by Siddharth Rout
If I have understood you correctly, then you don't need VBA for this. Simply highlight the relevant range and not just column Q while conditional formatting. See this example
如果我理解正确，那么您不需要 VBA。只需在条件格式设置时突出显示相关范围，而不仅仅是列 Q。看这个例子
回答by pnuts
I have not bothered with cutoffs since for example you have not specified the formatting you required for a cell in ColumnQ with value -5.
我没有为截断而烦恼，因为例如您没有指定 ColumnQ 中带有 value 的单元格所需的格式-5。
For consistency with the details in your link, I have assumed that the likes of values >-5refer to -5%rather than -5.
为了与您链接中的详细信息保持一致，我假设 是values >-5指-5%而不是-5。
To change the color of the cells in column P so they are equal to their "matching" cells in column Q, without ruining the formulas created in column P apply Conditional Formatting (which does not alter the actual cell content, only its appearance).
要更改 P 列中单元格的颜色，使其等于 Q 列中的“匹配”单元格，而不破坏 P 列中创建的公式，请应用条件格式（它不会改变实际的单元格内容，只会改变其外观）。
To format ColumnP cells according to rules determined by ColumnQ cells adjust the range to which the formatting is applied. Roughly, determine the format for Q and apply that to P:Q.
要根据 ColumnQ 单元格确定的规则格式化 ColumnP 单元格，请调整应用格式的范围。粗略地，确定 Q 的格式并将其应用于 P:Q。
Since three colours are involved CF will require at least two rules where determined by formulae (a third colour could be applied by 'standard' fill, which CF rules override).
由于涉及三种颜色，CF 将需要至少两个由公式确定的规则（第三种颜色可以通过“标准”填充应用，CF 规则会覆盖）。
Formula rules as below appear to meet my limited understanding of your requirements:
以下公式规则似乎满足我对您的要求的有限理解：
Green: =$Q4>-0.05
Yellow: =AND($Q4>-0.25,$Q4<0.05)
Red: =$Q4<0.25
绿色：=$Q4>-0.05
黄色：=AND($Q4>-0.25,$Q4<0.05)
红色：=$Q4<0.25
For the example I chose the applicable range for each as:
对于示例，我为每个选择了适用范围：
=$P:$Q
Note:
笔记：
- These do not directly address transition values such as -5%
- Empty cells will also be formatted
- The order of the rules makes a difference
- 这些不直接解决过渡值，例如 -5%
- 空单元格也将被格式化
- 规则的顺序有所不同
