Code to highlight rows when data range is before previous date.

[Dazzawm]

Tricky one to explain this. I have a file where I need to know if any row of dates are earlier than the rows before. The code first needs to look at column K and all the numbers that match. In the example below I have highlighted in yellow the ones that are incorrect. If you look at N6&7/O6&7 the date is earlier than P5/Q5 above. The same with N12&13/O12&13 is earlier than P11/Q11.

The ones in blue are as it should be, they all follow on from each other. Like I say difficult to explain but please let me know if more clarification is needed.

Excel 2010

	K	L	M	N	O	P	Q
1	Identifier			SM	SY	EM	EY
2	M10306960000001			03	1994	12	1997
3	M10306960000001			03	1994	12	1997
4	M10306960000001			03	1994	12	1997
5	M10306960000002			03	1994	12	1995
6	M10306960000002			03	1994	12	1999
7	M10306960000002			03	1994	12	1999
8	M18912550000000			06	2016	12	2016
9	M18912550000000			06	2016	12	2016
10	M18912550000000			06	2016	12	2016
11	M18900900000001			02	2015	12	2015
12	M18900900000001			02	2015
13	M18900900000001			02	2015
14	M18900900000002			02	2015
15	M18900900000002			02	2015
16	M18900900000002			02	2015
17	M18912170000009			04	2015	04	2017
18	M18912170000009			04	2016	04	2017
19	M18912170000002			02	2015	12	2015
20	M18912170000002			01	2016	12	2016
21	M18912170000002			01	2017
22	M18912170000007			05	2016
23	M18912170000007			05	2016
24	M18912170000007			05	2016

<colgroup><col style="width: 25pxpx" https:="" www.mrexcel.com="" forum="" usertag.php?do="list&action=hash&hash=DAE7F5"" target="_blank"></colgroup><colgroup><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

​

Edit

In hindsight its probably better if the code looks for matches in column C rather than K please.

 

[oldbrewer]

"If you look at N6&7/O6&7 the date is earlier than P5/Q5 above.2

N6 is 03 P5 is 12 - I do not follow - are N and P both months ?

 

[Dazzawm]

Yes I am guessing the code would need to put months and years together like 12/2015 and 02/2015. Then it would work out that the rows below are earlier than above. I said it was hard to explain!

 

[oldbrewer]

If you look at N6&7/O6&7 the date is earlier than P5/Q5 above. The same with N12&13/O12&13 is earlier than P11/Q11.

I just don't underestand - in plain english, why is row 5 colored yellow ?

 

[Dazzawm]

If you look at N6&7/O6&7 the date is earlier than P5/Q5 above. The same with N12&13/O12&13 is earlier than P11/Q11.

I just don't underestand - in plain english, why is row 5 colored yellow ?

Its just because the 3 rows match in column K, to show those group are wrong. Basically when K matches the dates in the rows below can only be the same date or after, never earlier.

 

[iggy_]

Why not just use conditional formatting, its not exactly as you want the result but give you an area of issues:

Excel 2016 (Windows) 32 bit

	K​	L​	M​	N​	O​	P​	Q​
1​	Identifier			SM	SY	EM	EY
2​	M10306960000001			3​	1994​	12​	1997​
3​	M10306960000001			3​	1994​	12​	1997​
4​	M10306960000001			3​	1994​	12​	1997​
5​	M10306960000002			3​	1994​	12​	1995​
6​	M10306960000002			3​	1994​	12​	1999​
7​	M10306960000002			3​	1994​	12​	1999​
8​	M18912550000000			6​	2016​	12​	2016​
9​	M18912550000000			6​	2016​	12​	2016​
10​	M18912550000000			6​	2016​	12​	2016​
11​	M18900900000001			2​	2015​	12​	2015​
12​	M18900900000001			2​	2015​
13​	M18900900000001			2​	2015​
14​	M18900900000002			2​	2015​
15​	M18900900000002			2​	2015​
16​	M18900900000002			2​	2015​
17​	M18912170000009			4​	2015​	4​	2017​
18​	M18912170000009			4​	2016​	4​	2017​
19​	M18912170000002			2​	2015​	12​	2015​
20​	M18912170000002			1​	2016​	12​	2016​
21​	M18912170000002			1​	2017​
22​	M18912170000007			5​	2016​
23​	M18912170000007			5​	2016​
24​	M18912170000007			5​	2016​

<tbody>
</tbody>

Sheet: Sheet1

<tbody>
</tbody>

Excel 2016 (Windows) 32 bit

	S​
2​	=AND(K2=K1,OR(IFERROR(DATE(Q2,P2,1),0)<>IF+$N$1:$Q$24ERROR(DATE(Q1,P1,1),0),IFERROR(DATE(O2,N2,1),0)<>IFERROR(DATE(O1,N1,1),0)),DATE(O2,N2,1)<iferror(date(q1,p1,1),0))< div=""></iferror(date(q1,p1,1),0))<>​

<tbody>
</tbody>

Sheet: Sheet1

<tbody>
</tbody>

 

[Dazzawm]

Thanks I will try but I preferred a macro as there 100s of thousands of rows

 

[iggy_]

Is the macro just highlighting the subset as you did in your example? What is in Column C that you say should be used instead of column K?

 

[Dazzawm]

Yes for reasons that will only complicate, the code needs to look at C rather than K

 

[oldbrewer]

where and what is column C