每次更改值时Excel更改行颜色(非连续) [英] Excel Change Row color at every change in value (non consecutive)

问题描述

在Excel中，我想在特定行值的每次更改时更改行的颜色.更改后的行可能是非连续的.

In Excel, I want to change the color of the row at every change in a particular row value. The changed rows could be non-consecutive.

例如:

Customer Product Line  Product

1        Outdoor       Lawn Jarts
1        Indoor        Nerf Hoops
2        Outdoor       Ball Game
3        Indoor        Fort Builder
4        Indoor        Twist'Em
4        Indoor        Silly Silly

在此示例中，我希望客户1为浅蓝色；客户2，白；客户3浅蓝色；客户4，白色，每次客户变更时都会重复.

In this example, I would want customer 1 to be light blue; customer 2, white; customer 3 light blue; customer 4, white, repeating at every customer change.

如果可能的话，我宁愿使用公式. 谢谢.

I'd prefer to use a formula if possible. Thanks.

客户编号是非连续编号(1、15、49406等)

Customer numbers are non-consecutive numbers (1,15,49406, etc.)

推荐答案

从A2开始，突出显示范围A2:C7(或任何必要的范围)，然后在条件格式设置规则管理器中创建一个新规则.选择使用公式来确定要格式化的单元格"，然后输入以下公式:

Starting in A2, highlight the Range A2:C7 (Or whatever range necessary) and then create a New Rule in the Conditional Formatting Rules Manager. Select "Use a formula to determine which cells to format" and enter this formula:

=MOD(SUM(IF(FREQUENCY($A$2:$A2,$A$2:$A2)>0,1)),2)=1

如果客户编号中包含文字，则可以使用:

If the customer numbers include text, you can use:

=MOD(SUM(IF(FREQUENCY(MATCH($A$2:$A2,$A$2:$A2,0),MATCH($A$2:$A2,$A$2:$A2,0))>0,1)),2)=1

这篇关于每次更改值时Excel更改行颜色(非连续)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！