excel - 条件格式行

Question

所以基本上我想在每次 A 列（初始编号）中的值更改时更改整行的颜色。理想情况下，我希望行的颜色介于深灰色和白色之间。

似乎没有适合此的特定明显的新条件格式规则。我尝试使用内置的唯一和重复条件格式规则，但这似乎没有帮助。我会继续尝试并在这里更新。

Initiative Number (A)   Initiative (B)
I9532                   Initiative I9532: Alberta: Platform Readiness
I8235                   Initiative I8235: Rationalisation of Card Holder      
I8638                   Initiative I8638: EDW - MEA - MTF/ PROD/ DR 
I8638                   Initiative I8638: EDW - MEA - MTF/ PROD/  
I7089                   Initiative I7089: MIP Connectivity for Cloud Customer  
I7089                   Initiative I7089: MIP Connectivity for Cloud Customer  
I7089                   Initiative I7089: MIP Connectivity for Cloud Customer  
I7089                   Initiative I7089: MIP Connectivity for Cloud Customer 
I7089                   Initiative I7089: MIP Connectivity for Cloud Customer
I7089                   Initiative I7089: MIP Connectivity for Cloud Customer 
I1618                   Initiative I1618: UTS - Standardization  
I6018                   Initiative I6018: EDW-MEA-BI-PTS Release and Stage 
I2739                   Initiative I2739: [DPS] Launch ECMS
I2739                   Initiative I2739: [DPS] Launch ECMS
I2739                   Initiative I2739: [DPS] Launch ECMS

标签： excel

Answer 1

You can use a helper column and conditional formatting based on a formula to accomplish this.

• In the helper column (column C), add this formula starting in C2: =IF(A2<>A1,IF(C1=4,1,C1+1),C1). Change the instances of 4 to however many colors you want to use.
• This will then return 1, 2, 3, 3, 4, 4, 4...., 1, 2, 3, 3, 3 representing whether the Initiative Number has changed from the row above, and also what color the row should be - each number represents a different color.
• Assuming you are working with 4 different colors, add 4 new conditional formatting rules based on the formulas =$C2=1, =$C2=2, =$C2=3, and =$C2=4, where each rule applies a different fill color.
• Continuing with the example, make sure that the "Applies to" Range is $A$2:$B$16.

You can hide the helper column if needed but included in the screenshot for reference.