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

Can colors repeat? I.e. can you have white, light grey, medium grey, dark grey, and white again? — BigBen
– BigBen, Commented Sep 12, 2019 at 15:08
Absolutely. That's actually ideal, just as long as they don't repeat with consecutive unique values in Column A — Sahil Piyarali
– Sahil Piyarali, Commented Sep 12, 2019 at 15:15
Have you tried anything? SO expects user to put in some efforts. Is use of helper column acceptable? — shrivallabha.redij
– shrivallabha.redij, Commented Sep 12, 2019 at 15:23

BigBen · Accepted Answer · 2019-09-12 19:00:17Z

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.

Justyna MK · Accepted Answer · 2019-09-12 18:20:07Z

0

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:

answered Sep 12, 2019 at 18:20

Justyna MK

3,5533 gold badges14 silver badges25 bronze badges

Collectives™ on Stack Overflow

Conditional Formatting Rows

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related