vba 条件格式 - 基于一列颜色缩放整行

Conditional Formatting - Color Scale entire row based on one column

提问by Gaurav Singhal

Suppose I want to color scale complete rows on the basis of values in a column (using excel inbuilt color scale option in the conditional formatting menu). How do I achieve this? Please see the following image

假设我想根据列中的值对完整行进行颜色缩放（使用条件格式菜单中的excel内置颜色比例选项）。我如何实现这一目标？请看下图

采纳答案by Gaurav Singhal

I found a property Range.DisplayFormat.Interior.Colorin this post, at Mrexcel. Using this property I was able to get color of conditionally format cell and use it for the other rows. Catch is, it works only excel 2010 onwards. I have excel 2010 so it worked for me. Here is the exact code -

我在 Mrexcel 的Range.DisplayFormat.Interior.Color这篇文章中找到了一处房产。使用此属性，我能够获取条件格式单元格的颜色并将其用于其他行。Catch 是，它仅适用于 2010 年以后的 excel。我有 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

回答by oersted88

If I understood you correctly I have been battling with the same issue. That is to format entire rows based on the values in one column, wherein the values have been formatted via Excel's Color Scales.

如果我理解正确的话，我一直在与同样的问题作斗争。即根据一列中的值格式化整行，其中值已通过 Excel 的色阶格式化。

I found this truly ridiculouslyeasy workaround that involves copying your color scaled cells into word, then back into excel after which you can delete the values and substitute them with whatever values you want without changing the format:

我发现这真的可笑的简单的解决方法，涉及到复制你的颜色缩放细胞转化为文字，然后回到到Excel之后，你可以用任何你想要的值，而不改变文件格式，删除的价值和替代它们：

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

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

All credit to user Raystafarian

全部归功于用户 Raystafarian

回答by Rick supports Monica

You don't need VBA to do this, really.

你不需要 VBA 来做到这一点，真的。

One thing to keep in mind here is that you won't be able to achieve your desired behavior with a single conditional formatting rule; you'll have to have a separate rule for each sales-based row color definition. A second thing: I have found that it is much easier to achieve desired Conditional Formatting behavior in Excel using Named Ranges for the rules instead of regular formulas.

这里要记住的一件事是，您将无法使用单个条件格式规则来实现您想要的行为；您必须为每个基于销售的行颜色定义制定单独的规则。第二件事：我发现使用命名范围作为规则而不是常规公式在 Excel 中实现所需的条件格式行为要容易得多。

With these issues in mind, follow these steps to create your named range and then create your conditional formatting rules.

考虑到这些问题，请按照以下步骤创建命名范围，然后创建条件格式规则。

1. First, select the first sales cell on your sheet (uppermost row)
2. Next, give the cell a name, "SALES". Do this by pressing Ctl+F3, or select Formulas->Name Managerfrom the ribbon. Then select New... In Name:enter SALESand in Refers to:enter =$XNwhere X is the column of the first sales cell, and N is the row number. Hit Enter.
3. Now select the entire cell range you wish to exhibit this behavior
4. Select Home->Conditional Formatting->New Rule...
5. Select Use a Formula to Determine Which Cells to Formatand enter =SALES=numberwhere number is the sales number you wish to trigger a color
6. Select Formatand the Filltab. Now you need to decide what background color you want for the sales number you chose. You can also choose other formatting options, like the font color, etc.
7. Hit OK, OK, OK. Repeat steps 3 to 6 for each different sales figure/color combination you want. If you want a color for "all sales less than X", in your rule you will enter =SALES<number(< is "less than"; you can also do <=, which is "less than OR equal to"). If want the rule to happen when between two numbers, you can do =AND(SALES<=CEILING, SALES>=FLOOR), where ceiling and floor are the upper and lower bounds. If you want a color for "all sales greater than X", you can do =SALES>number.

1. 首先，选择工作表上的第一个销售单元格（最上面一行）
2. 接下来，给单元一个名称，“销售”。通过按 Ctl+F3 或从功能区中选择Formulas->Name Manager来执行此操作。然后选择New..。在Name:输入SALES和Refers to:输入中=$XN，X 是第一个销售单元格的列，N 是行号。击中Enter。
3. 现在选择您希望展示此行为的整个单元格范围
4. 选择Home-> Conditional Formatting->New Rule...
5. 选择Use a Formula to Determine Which Cells to Format并输入=SALES=number数字是您希望触发颜色的销售编号
6. 选择Format和Fill选项卡。现在您需要决定您选择的销售编号的背景颜色。您还可以选择其他格式选项，例如字体颜色等。
7. 点击OK，OK，OK。为您想要的每个不同的销售数字/颜色组合重复步骤 3 到 6。如果您想要“所有销售额小于 X”的颜色，请在您的规则中输入=SALES<number（< 是“小于”；您也可以执行 <=，即“小于或等于”）。如果希望规则发生在两个数字之间，您可以这样做=AND(SALES<=CEILING, SALES>=FLOOR)，其中天花板和地板是上限和下限。如果您想要“所有销售额大于 X”的颜色，您可以执行=SALES>number.

EDIT:

编辑：

To make entering your conditional formulas a bit easier, you can use the "Stop If True" feature. Go to Home->Conditional Formatting->Manage Rules, and in the dropdown menu choose This Worksheet. Now you will see a list of all the rules that apply to your sheet, and there will be a "Stop If True" checkbox to the right of each rule.

为了更轻松地输入条件公式，您可以使用“如果为真则停止”功能。转到Home-> Conditional Formatting-> Manage Rules，然后在下拉菜单中选择This Worksheet。现在，您将看到适用于您的工作表的所有规则的列表，并且每个规则的右侧都会有一个“如果为真则停止”复选框。

For each row color rule, put a check in the "Stop If True" checkbox. Now your formulas can be like this (just for example):

对于每行颜色规则，勾选“如果为真则停止”复选框。现在你的公式可以是这样的（只是举例）：

• =Sales>25for the green rule
• =Sales>10for the yellow rule
• =Sales>0for the Red rule

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

Etc, instead of like this:

等等，而不是这样：

• =AND(Sales>0,Sales<=10)for the Red rule
• =AND(Sales>10,Sales<=25)for the yellow rule
• =Sales>25for the green rule

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

The Stop If Truebox means that once a formatting rule has been applied to a cell, that cell will not be formatted again based on any other rules that apply to it. Note this means that the order of the rules DOES MATTER when using Stop If True.

该Stop If True框意味着一旦将格式规则应用于单元格，该单元格将不会根据适用于它的任何其他规则再次进行格式设置。请注意，这意味着规则的顺序在使用Stop If True.

回答by Richard Vicary

I think I have found a solution for this. I can achieve a colour scale of 5 degrees for any range of numbers across all cells in the row with the option of only affecting cells containing data.

我想我已经找到了解决方案。我可以为行中所有单元格的任何数字范围实现 5 度的色阶，并且可以选择仅影响包含数据的单元格。

This is achieved by creating 5 conditional formatting rules based around the following:

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

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

The first argument in the ANDfunction D4<>""is used if you only want cells containing data to be affected, remove this if you want the whole row of data colour coded.

如果您只希望包含数据的单元格受到影响，则使用该AND函数中的第一个参数，D4<>""如果您希望对整行数据进行颜色编码，则将其删除。

The second argument, $D4<>""points to the cell in the row that contains the value to evaluate - remember the $to lock the column

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

The third argument, $D4-(MIN($D$4:$D$20)-1))/(MAX($D$4:$D$20)-(MIN($D$4:$D$20)-1))*5<=2evaluates the position of the value within the entire range of values and converts this into a number between 1 and 5, changing the *5at the end of this argument allows you to have more steps in your colour sequence. You will need to add more conditional rules accordingly. The <=2indicates this is the second colour step in the sequence.

第三个参数$D4-(MIN($D$4:$D$20)-1))/(MAX($D$4:$D$20)-(MIN($D$4:$D$20)-1))*5<=2计算值在整个值范围内的位置，并将其转换为 1 到 5 之间的数字，更改*5此参数末尾的 允许您在颜色序列中有更多步骤。您将需要相应地添加更多条件规则。该<=2结果表明是该序列中的第二颜色的步骤。

Colours 3 and 4 use the same condition but the <=2is changed to <=3and <=4respectively.

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

The first and last colour stop need a small modification if you always want the lowest number in the range to be the first colour stop and the highest number in the range to be the highest number stop.

如果您始终希望范围中的最低数字作为第一个色标，而该范围中的最高数字作为最高数字标定，则第一个和最后一个色标需要稍作修改。

For the minimum number in the range, adapt as follows:

对于范围内的最小数，调整如下：

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

the introduction of OR($D4=MIN($D$4:$D$20)catches the first number in the range

引入OR($D4=MIN($D$4:$D$20)捕获范围中的第一个数字

Similarly

相似地

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

Using OR($D4=MAX($D$4:$D$20)catches the maximum number in the range

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

Note that Stop if Truemust be ticked for all conditions and the conditions must be sorted from minimum to maximum steps in the sequence.

请注意，Stop if True必须为所有条件打勾，条件必须按顺序从最小到最大步数排序。

Image of Conditional Formatting Rules Manager

条件格式规则管理器的图像

回答by Miss Palmer

You can do this with the standard conditional formatting menu, no need for VBA. You choose the option of specifying your own formula, and you can refer to a cell (lock the column with the '$') other than the one you want to highlight.

您可以使用标准条件格式菜单执行此操作，无需 VBA。您选择指定自己的公式的选项，并且您可以引用要突出显示的单元格以外的单元格（用“$”锁定该列）。

Background Reading

背景阅读