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

[TABLE="width: 330"]
<tbody>[TR]
[TD="width: 110, bgcolor: transparent"][/TD]
[TD="width: 110, bgcolor: transparent"][/TD]
[TD="width: 110, bgcolor: transparent"][/TD]
[TD="width: 110, bgcolor: transparent"]column "K"
[/TD]
[TD="width: 110, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 110"]First
[/TD]
[TD="width: 110"]Last
[/TD]
[TD="width: 110"]Certified
[/TD]
[TD="width: 110"]Expires
[/TD]
[TD="width: 110"]Days left
[/TD]
[/TR]
[TR]
[TD="width: 110"]Traci

[/TD]
[TD="width: 110"]Brown
[/TD]
[TD="width: 110"]

5/09/2016​

[/TD]
[TD="width: 110"]

5/09/2018​

[/TD]
[TD="width: 110"]

0
​

[/TD]
[/TR]
[TR]
[TD="width: 110"]Mary

[/TD]
[TD="width: 110"]Hannan
[/TD]
[TD="width: 110"]

21/03/2016​

[/TD]
[TD="width: 110"]

5/09/2018​

[/TD]
[TD="width: 110"]

0
​

[/TD]
[/TR]
[TR]
[TD="width: 110"]Linda
[/TD]
[TD="width: 110"]Thomas
[/TD]
[TD="width: 110"]

28/02/2016​

[/TD]
[TD="width: 110"]

28/02/2018​

[/TD]
[TD="width: 110"]

0​

[/TD]
[/TR]
[TR]
[TD="width: 110"]Sarah
[/TD]
[TD="width: 110"]Burton
[/TD]
[TD="width: 110"]

18/10/2016​

[/TD]
[TD="width: 110"]

28/02/2018​

[/TD]
[TD="width: 110"]

0​

[/TD]
[/TR]
[TR]
[TD="width: 110"]Kevin
[/TD]
[TD="width: 110"]Grizzle
[/TD]
[TD="width: 110"]

13/04/2016​

[/TD]
[TD="width: 110"]

28/02/2018​

[/TD]
[TD="width: 110"]

0​

[/TD]
[/TR]
[TR]
[TD="width: 110"]Chad
[/TD]
[TD="width: 110"]Jackson
[/TD]
[TD="width: 110"]

12/10/2016​

[/TD]
[TD="width: 110"]

28/02/2018​

[/TD]
[TD="width: 110"]

0​

[/TD]
[/TR]
[TR]
[TD="width: 110"]Alene
[/TD]
[TD="width: 110"]Helsel
[/TD]
[TD="width: 110"]

17/04/2016​

[/TD]
[TD="width: 110"]

12/10/2018​

[/TD]
[TD="width: 110"]

0​

[/TD]
[/TR]
[TR]
[TD="width: 110"]Jason
[/TD]
[TD="width: 110"]Ward
[/TD]
[TD="width: 110"]

16/03/2016​

[/TD]
[TD="width: 110"]

16/03/2018​

[/TD]
[TD="width: 110"]

0
​

[/TD]
[/TR]
[TR]
[TD="width: 110"]Edward
[/TD]
[TD="width: 110"]Taylor
[/TD]
[TD="width: 110"]

22/12/2015​

[/TD]
[TD="width: 110"]

22/12/2017​

[/TD]
[TD="width: 110"]

0
​

[/TD]
[/TR]
[TR]
[TD="width: 110"]Debbie
[/TD]
[TD="width: 110"]Jackson
[/TD]
[TD="width: 110"]

15/10/2016​

[/TD]
[TD="width: 110"]

22/12/2017​

[/TD]
[TD="width: 110"]

0​

[/TD]
[/TR]
[TR]
[TD="width: 110"]Lucy
[/TD]
[TD="width: 110"]Copeland
[/TD]
[TD="width: 110"]

1/05/2016​

[/TD]
[TD="width: 110"]

22/12/2017​

[/TD]
[TD="width: 110"]

0​

[/TD]
[/TR]
[TR]
[TD="width: 110"]Elaine
[/TD]
[TD="width: 110"]Evatt
[/TD]
[TD="width: 110"]

5/04/2016​

[/TD]
[TD="width: 110"]

22/12/2017​

[/TD]
[TD="width: 110"]

0​

[/TD]
[/TR]
[TR]
[TD="width: 110"]Robert
[/TD]
[TD="width: 110"]Williams
[/TD]
[TD="width: 110"]

3/11/2016​

[/TD]
[TD="width: 110"]

22/12/2017​

[/TD]
[TD="width: 110"]

0​

[/TD]
[/TR]
[TR]
[TD="width: 110"]Rita
[/TD]
[TD="width: 110"]Helms
[/TD]
[TD="width: 110"]

1/04/2016​

[/TD]
[TD="width: 110"]

22/12/2017​

[/TD]
[TD="width: 110"]

0​

[/TD]
[/TR]
[TR]
[TD="width: 110"]Richard
[/TD]
[TD="width: 110"]Hall
[/TD]
[TD="width: 110"]

9/06/2016​

[/TD]
[TD="width: 110"]

22/12/2017​

[/TD]
[TD="width: 110"]

0
​

[/TD]
[/TR]
[TR]
[TD="width: 110"]Mark
[/TD]
[TD="width: 110"]Kroeger
[/TD]
[TD="width: 110"]

5/01/2016​

[/TD]
[TD="width: 110"]

5/01/2018​

[/TD]
[TD="width: 110"]

0
​

[/TD]
[/TR]
[TR]
[TD="width: 110"]Carolyn
[/TD]
[TD="width: 110"]Perry
[/TD]
[TD="width: 110"]

12/03/2016​

[/TD]
[TD="width: 110"]

5/01/2018​

[/TD]
[TD="width: 110"]

0
​

[/TD]
[/TR]
</tbody>[/TABLE]

 

[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.