Conditional Formatting Rows

Question

So basically I want to change the color of the entire row every time the value in column A (Initiative Number) changes. Ideally I want the colors of the rows to be between dark grey and white.

There doesn't seem to be a specific obvious new conditional formatting rule that fits this. I have tried using the built in conditional formatting rule of uniques and duplicates but that doesn't seem to be helping. Will continue to try things and update here as I do so.

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

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.

Answer 2

Alternatively, if you do not want to repeat the color formatting with the consecutive unique values in column A (i.e. you simply want to highlight the first occurence of the unique value), you can try the following: