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.

 

[Dazzawm]

where and what is column C

C is just a different set of data that matches just like K

 

[oldbrewer]

M10306960000001			03	1994	12	1997
5	M10306960000002			03	1994	12	1995 is the 001 the same as 002

<tbody>
</tbody>

 

[Dazzawm]

No its a different number, they need to match in C (K)

 

[oldbrewer]

sorry - but I still do not know what you want - try asking again with a pretend scenario eg fruit prices or whatever

 

[Dazzawm]

1. Data needs to match in column C.
2. It then needs to look at start and end dates.
3. If the rows match in C the date range in N & O should always be equal or later to the date range of P & Q of the previous row.

 

[oldbrewer]

row 5 first time number has appeared so why is it yellow - if it is the same in row C why not show us rows C to Q

 

[Dazzawm]

I don't think it is necessary for you to see what is between C & Q plus its sensitive data. And I don't understand your post 12, I don't know where the 5 came from.

 

[oldbrewer]

sensitive data - overwrite it with random numbers

id	date	amount	lower
1	01/04/2018	27
2	02/04/2018	31
3	03/04/2018	35
4	04/04/2018	39
1	05/04/2018	43
2	01/04/2018	47	L
3	07/04/2018	51
4	08/04/2018	55
1	09/04/2018	59
2	10/04/2018	63
3	11/04/2018	67
4	12/04/2018	71
1	08/04/2018	75	L
2	14/04/2018	79
3	15/04/2018	83
4	16/04/2018	87
	is this correct

<colgroup><col><col><col span="3"></colgroup><tbody>
</tbody>

 

[wideboydixon]

In your original post, row 3 (03/1994) is less than row 2 (12/1997) yet it's not flagged as incorrect. What makes this row correct? Also, if an error is found, do you want to highlight all rows containing that identifier?

WBD

 

[wideboydixon]

Quick first draft of code give this:

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

WBD