条件格式——基于一列对整行进行颜色缩放

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内置的颜色比例选项)。我如何做到这一点?请看下面的图片。

4 个解决方案

#1

I found a property Range.DisplayFormat.Interior.Color in 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 -

我找到了属性范围。displayformat。interior。在Mrexcel，给这封信上色。使用此属性，我可以获得有条件格式单元格的颜色，并将其用于其他行。Catch只有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

#2

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 ridiculously easy 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:

我发现这是一个非常简单的解决方案，它涉及到将颜色缩放的单元格复制到word中，然后再复制到excel中，在此之后，您可以删除这些值，并使用任何您想要的值替换它们，而无需更改格式:

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

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

All credit to user Raystafarian

所有功劳归于用户Raystafarian

#3

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.

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

First, select the first sales cell on your sheet (uppermost row)
首先，选择表上的第一个销售单元格(最上面一行)
Next, give the cell a name, "SALES". Do this by pressing Ctl+F3, or select Formulas->Name Manager from the ribbon. Then select New... In Name: enter SALES and in Refers to: enter =$XN where X is the column of the first sales cell, and N is the row number. Hit Enter.
接下来，给单元格命名为“SALES”。通过按Ctl+F3，或者从功能区中选择formula ->名称管理器。然后选择新…Name:输入SALES, In为:enter =$XN，其中X为第一个SALES单元的列，N为行号。回车。
Now select the entire cell range you wish to exhibit this behavior
现在选择您希望显示此行为的整个单元格范围
Select Home->Conditional Formatting->New Rule...
选择回家- >条件格式- >新规则……
Select Use a Formula to Determine Which Cells to Formatand enter =SALES=number where number is the sales number you wish to trigger a color
选择使用一个公式来确定要格式化哪些单元格，输入=SALES=number，其中number是您希望触发颜色的销售编号
Select Format and the Fill tab. 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.
选择Format和Fill选项卡。现在，您需要决定您所选择的销售号码的背景色。您还可以选择其他格式选项，如字体颜色等。
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.
点击确定,好,好。对于您想要的每个不同的销售数字/颜色组合，重复步骤3到6。如果您想要“所有销售额小于X”的颜色，在规则中您将输入= sales =FLOOR)，其中CEILING和FLOOR是上界和下界。如果您想要“所有销售额大于X”的颜色，您可以做= sales >号。 (

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->条件格式->管理规则，在下拉菜单中选择此工作表。现在，您将看到适用于您的表的所有规则的列表，每个规则的右边将有一个“如果为真则停止”复选框。

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>25 for the green rule
=销售>25的绿色规则
=Sales>10 for the yellow rule
=销售>10黄色规则。
=Sales>0 for the Red rule
=销售>

Etc, instead of like this:

等等，而不是这样:

=AND(Sales>0,Sales<=10) for the Red rule
=和(Sales>0,Sales<=10
=AND(Sales>10,Sales<=25) for the yellow rule
= (Sales>10,Sales<=25
=Sales>25 for the green rule
=销售>25的绿色规则

The Stop If True box 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(如果为真)时，规则的顺序很重要。

#4

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

背景阅读

#1

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

#2