条件格式化 - 基于一列，使用颜色刻度标尺来对整行进行着色

Question

假设我想根据某一列中的值对整行进行颜色缩放（使用Excel 条件格式菜单中内置的颜色缩放选项）。 我该如何实现？ 请参见以下图像

- Gaurav Singhal

Answer 1

我在这个帖子中发现了一个名为Range.DisplayFormat.Interior.Color的属性，它与编程有关。使用此属性，我能够获取有条件格式的单元格的颜色并将其用于其他行。但是，它仅适用于Excel 2010及以上版本。我有Excel 2010，所以它对我有效。

以下是确切的代码 -

For i = rowStart To rowEnd
    For j = columnStart To columnEnd
        Cells(i, j).Interior.Color = Cells(i, 4).DisplayFormat.Interior.Color
    Next
Next

- Gaurav Singhal

---

我喜欢这个解决方案，但我猜它和使用 Word 的解决方法有相同的限制 - 当数据发生变化时，这些单元格不会动态更新，你需要再次运行宏来更新它们。尽管如此，我认为这是我们目前最好的选择（如果你想要真正连续的颜色比例）。 - Tom Wagstaff

2023年的Microsoft 365中，是否有任何简单的解决方案可用于Microsoft Excel？ - TVG

Answer 2

你不需要使用VBA实现这个，真的不需要。但是首先有两件事要指出：第一，你不能通过单个条件格式规则来实现你想要的行颜色定义；你必须为每个基于销售额的行颜色定义设置一个独立的规则。第二，我发现在Excel中使用命名区域来设置规则要比使用普通公式更容易实现期望的条件格式。

如果你仍然赞成我的观点，请按照以下步骤创建命名区域并创建条件格式规则：

1. 首先，选择工作表上的第一个销售单元格（最上面的行）。 2. 接下来，给该单元格命名为“SALES”。方法是按下Ctl+F3，或从功能区中选择“公式”->“名称管理器”，然后选择“新建…”。在“名称:”中输入“SALES”，在“引用:”中输入“=$XN”，其中X是第一个销售单元格所在的列，N是行号。按下“Enter”。 3. 现在选择您希望呈现此行为的整个单元格范围。 4. 选择“开始”->“条件格式”->“新建规则…”。 5. 选择“使用公式确定要设置格式的单元格”，并输入“=SALES=number”，其中number是您希望触发颜色的销售额。 6. 选择“格式”和“填充”选项卡。现在，您需要决定所选销售数字的背景颜色。您还可以选择其他格式选项，如字体颜色等。 7. 点击“确定”，然后重复步骤3到6，为每个不同的销售数字/颜色组合设置规则。如果您想要一个“所有销售额小于X”的颜色，在您的规则中输入“=SALES=FLOOR)”（其中ceiling和floor是上限和下限）。如果要设置“所有销售额大于X”的颜色，则可以使用“=SALES>number”。 编辑：

为了更轻松地输入条件公式，您可以使用“如果为真停止”功能。转到“开始”->“条件格式”->“管理规则”，并在下拉菜单中选择“此工作表”。现在，您将看到适用于工作表的所有规则的列表，并且每个规则右侧将有一个“如果为真停止”复选框。

对于每个行颜色规则，请在“停止计算”复选框中打勾。现在你的公式可以像这样（仅为示例）：

• 绿色规则： =Sales>25
• 黄色规则： =Sales>10
• 红色规则： =Sales>0

等等，而不是像这样：

• 红色规则：=AND(Sales>0,Sales<=10)
• 黄色规则：=AND(Sales>10,Sales<=25)
• 绿色规则：=Sales>25

“停止计算”框意味着一旦对一个单元格应用了格式规则，该单元格将不会再基于任何其他适用于它的规则进行格式化。请注意，这意味着使用“停止计算”时规则的顺序很重要。

- Rick

---

我觉得我需要更好地表达我的问题，我需要它用于 Excel 提供的“颜色刻度”选项。我不想自己指定颜色。虽然感谢这个答案，但真的很有见地，教会了我如何使用命名区域进行条件格式设置。我要练习一下 :) - Gaurav Singhal

@GauravSinghal，想要实现你想要的功能，可能最好的方法是为你的工作表编写一个事件，每当工作表重新计算时，将销售列的颜色应用于您范围内的行。不幸的是，我今天没有时间为您编写此代码，但如果您了解VBA，那应该足以让您入门。如果不了解，请学习一下。 - Rick

我记得之前在这种方法上遇到了瓶颈。使用VBA无法选择单元格有条件格式的颜色，所以我不得不从头开始计算规则。 - tea_pea

我正在尝试寻找答案，并在mrexcel的帖子中了解到了一个名为“Range.DisplayFormat.Interior.Color”的属性，它确实给出了有条件格式的颜色。感谢Palmer女士和Rick Teachey的帮助。我应该发表自己的答案吗？ - Gaurav Singhal

@MissPalmer 你可能会对此感兴趣。^^ - Rick

Answer 3

如果我理解正确的话，我一直在与同样的问题作斗争。那就是根据一列中的值格式化整行，在这种情况下，这些值已经通过Excel的颜色比例进行了格式化。

我发现了一个真正荒谬地简单的解决方法，它涉及将颜色比例缩放的单元格复制到Word中，然后再返回到Excel中，之后您可以删除值并用任何您想要的值替换它们而不改变格式。

https://superuser.com/questions/973921/copy-conditional-formatting-3-color-scheme-to-another-tab/973974#973974?newreg=fc5ca6d04a5a406fa39cd4796b6a539e

所有信用归用户Raystafarian所有

- oersted88

---

谁投了反对票，能解释一下原因吗？这似乎是我所问问题的一个不错的非VBA解决方案。 - Gaurav Singhal

我不是点赞者，但这种方法的一个缺点是当数据发生变化时，它不会自动更新颜色。如果电子表格中的更改使产品A的利润突然增加到12％，则产品A的线条仍将保持橙色，直到再次执行此解决方法。 - scottyc

有条件格式的整个关键在于它会根据单元格的值进行动态更新。这个答案完全移除了这种功能。 - Simon East

Answer 4

您可以使用标准条件格式设置菜单来完成此操作，无需使用VBA。您可以选择指定自己的公式选项，并且可以引用锁定列（使用'$'）中的单元格而不是要突出显示的单元格。 背景阅读

- tea_pea

---

我可以为“非颜色刻度”提供特定格式（背景、边框或字体颜色）。在这里，我想使用Excel内置的颜色刻度。 - Gaurav Singhal

好的。您可以手动完成，例如使用5种颜色渐变（例如，对于人口的前20％使用深红色，下一个20％使用浅红色等，将最大值和最小值提取到参考单元格中），但是如果您了解VBA，则在此阶段使用它可能更容易。 - tea_pea

Answer 5

我认为我已经找到了解决方案。我可以在行中的所有单元格上实现5度颜色比例，同时只影响包含数据的单元格。

这是通过创建基于以下条件的5个条件格式规则来实现的：

=AND(D4<>"",$D4<>"",($D4-(MIN($D$4:$D$20)-1))/(MAX($D$4:$D$20)-(MIN($D$4:$D$20)-1))*5<=2)

AND函数中的第一个参数D4<>""用于仅影响包含数据的单元格，如果要对整行数据进行颜色编码，则删除此参数。

第二个参数$D4<>""指向包含要评估值的行中的单元格 - 记住使用$锁定列。

第三个参数$D4-(MIN($D$4:$D$20)-1))/(MAX($D$4:$D$20)-(MIN($D$4:$D$20)-1))*5<=2评估值在整个值范围内的位置，并将其转换为介于1和5之间的数字，更改此参数末尾的*5可使您的颜色序列具有更多步骤。您需要相应地添加更多条件规则。 <=2表示这是序列中的第二个颜色步骤。

颜色3和4使用相同的条件，但将<=2更改为分别为<=3和<=4。

如果您希望范围内的最低数字始终是第一个颜色停止位置，而范围内的最高数字始终是最后一个颜色停止位置，则需要对第一个和最后一个颜色停止位置进行小修改。

对于范围内的最小数字，请按以下方式进行调整：

=AND(D4<>"",$D4<>"",OR($D4=MIN($D$4:$D$20),($D4-(MIN($D$4:$D$20)-1))/(MAX($D$4:$D$20)-(MIN($D$4:$D$20)-1))*5<=1))

OR($D4=MIN($D$4:$D$20)的介绍捕获了范围内的第一个数字。

同样地，

=AND(D4<>"",$D4<>"",OR($D4=MAX($D$4:$D$20),($D4-(MIN($D$4:$D$20)-1))/(MAX($D$4:$D$20)-(MIN($D$4:$D$20)-1))*5<=5))

使用OR($D4=MAX($D$4:$D$20)可以捕获范围内的最大数字

请注意，所有条件都必须勾选“停止计算”并且条件必须按照从最小到最大步骤的顺序排序。

条件格式规则管理器图像

- Richard Vicary