Visually group rows with conditional formatting

Question

I have a spreadsheet to which I periodically add some rows. When I do so, I have a date column with the date it was added. I'd like to use conditional formatting to highlight groups of rows where the date is the same.

Date	Foo	Bar	(Row color)
2016-07-13	4	0.2	Color 1
2016-07-13	12	3.8	Color 1
2016-07-13	5	3.1	Color 1
2016-07-29	108	11.5	Color 2
2016-07-29	25	6.4	Color 2
2016-08-04	8	0.2	Color 3
2016-08-04	37	2.3	Color 3
2016-08-04	3	5.1	Color 3

If it is possible to alternate two colors, that would be acceptable, but the ideal system...

• Uses a different color for each row...
  • ...until a new year is reached, at which point the colors start over (if possible)
• Is not wildly bright or super-hard on the eyes
• Is reasonable to distinguish one color from the colors that surround it

I can make a simple discriminator column with =if(A3=A2,B2,if(YEAR(A3)=YEAR(A2),B2+1,0)), but the only way I know to conveniently assign colors is with the "Color Scale," and it only colors the one cell, not its whole row.

Knowns

• Each date will have at least 5 rows, but may have as many as 15.
• There are probably less than 15 dates per year (though making separate rules for "discriminator=1, 2, 3, ..., 15" screams for better automation).

Answer 1

Alternating colors

If you want two alternating colors, you can set the following two rules (or only one if white is fine for the second color):

1. • Apply to range: A2:H

   • Custom formula is:

     =isodd(match($A2,unique($A$2:$A)))
     

2. Second rule (if you want another color too):

   • Apply to range: A2:H

   • Custom formula is:

     =iseven(match($A2,unique($A$2:$A)))
     

More colors

If you want all different colors for all 15 groups, you will need to set 15 rules, like:

=match($A2,unique($A$2:$A))=1
=match($A2,unique($A$2:$A))=2
=match($A2,unique($A$2:$A))=3
=match($A2,unique($A$2:$A))=4

and so on...