Conditional Format for row based on value in cell

[NZAS]

Hi
I have data set that changes regularly. When the data change what I want to have happen is to have the row colour like a table would base on the date in column K. i.e that is were the date is the same I and the row to be coloured. If only on colour an be used then colour the even or odd sets of dates with a colour
my example is below Rows 2 and 3 coloured
Row 4 to 7 no colour or lighter colour etc,etc where the same date is in Column "K"

Cheers
NZAS

			column "K"
First	Last	Certified	Expires	Days left
Traci	Brown	5/09/2016​	5/09/2018​	0 ​
Mary	Hannan	21/03/2016​	5/09/2018​	0 ​
Linda	Thomas	28/02/2016​	28/02/2018​	0​
Sarah	Burton	18/10/2016​	28/02/2018​	0​
Kevin	Grizzle	13/04/2016​	28/02/2018​	0​
Chad	Jackson	12/10/2016​	28/02/2018​	0​
Alene	Helsel	17/04/2016​	12/10/2018​	0​
Jason	Ward	16/03/2016​	16/03/2018​	0 ​
Edward	Taylor	22/12/2015​	22/12/2017​	0 ​
Debbie	Jackson	15/10/2016​	22/12/2017​	0​
Lucy	Copeland	1/05/2016​	22/12/2017​	0​
Elaine	Evatt	5/04/2016​	22/12/2017​	0​
Robert	Williams	3/11/2016​	22/12/2017​	0​
Rita	Helms	1/04/2016​	22/12/2017​	0​
Richard	Hall	9/06/2016​	22/12/2017​	0 ​
Mark	Kroeger	5/01/2016​	5/01/2018​	0 ​
Carolyn	Perry	12/03/2016​	5/01/2018​	0 ​

<tbody>
</tbody>

 

[Gerald Higgins]

What do you want to do with row 8, the only row that has that value ?

If this should be a different colour from row 7 (and the same as rows 2,3) then the rule you want to apply is not so much about grouping of similar results.
Instead it can be formulated as "flip the colour when the date changes".

One way to implement this in Excel is to use a helper column to flip between -1 and 1 every time the date changes.
Like this, in M2 (the cell to the right of 0 for Traci Brown)
=if(K2=K1,M1,M1*-1)
and copy all the way down column M.
Edit to add - put a value of 1 in cell M1 to start you off.

Column M can be hidden if you like.

Then apply Conditional Formating using column M, and apply to the whole row.
You can do this with one rule in CF - apply a default colour as one of your two colours, and make CF apply the other colour if the value in column M = -1.