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
Identifier			SM	SY	EM	EY
M10306960000001			03	1994	12	1997
M10306960000001			03	1994	12	1997
M10306960000001			03	1994	12	1997
M18912550000000			06	2016	12	2016
M18912550000000			06	2016	12	2016
M18912550000000			06	2016	12	2016
M18900900000002			02	2015
M18900900000002			02	2015
M18900900000002			02	2015
M18912170000007			05	2016
M18912170000007			05	2016
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>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FFFF00"]M10306960000002[/TD]
[TD="bgcolor: #FFFF00"][/TD]
[TD="bgcolor: #FFFF00"][/TD]
[TD="bgcolor: #FFFF00"]03[/TD]
[TD="bgcolor: #FFFF00"]1994[/TD]
[TD="bgcolor: #FFFF00"]12[/TD]
[TD="bgcolor: #FFFF00"]1995[/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #FFFF00"]M10306960000002[/TD]
[TD="bgcolor: #FFFF00"][/TD]
[TD="bgcolor: #FFFF00"][/TD]
[TD="bgcolor: #FFFF00"]03[/TD]
[TD="bgcolor: #FFFF00"]1994[/TD]
[TD="bgcolor: #FFFF00"]12[/TD]
[TD="bgcolor: #FFFF00"]1999[/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #FFFF00"]M10306960000002[/TD]
[TD="bgcolor: #FFFF00"][/TD]
[TD="bgcolor: #FFFF00"][/TD]
[TD="bgcolor: #FFFF00"]03[/TD]
[TD="bgcolor: #FFFF00"]1994[/TD]
[TD="bgcolor: #FFFF00"]12[/TD]
[TD="bgcolor: #FFFF00"]1999[/TD]

[TD="align: center"]8[/TD]

[TD="align: center"]9[/TD]

[TD="align: center"]10[/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: #FFFF00"]M18900900000001[/TD]
[TD="bgcolor: #FFFF00"][/TD]
[TD="bgcolor: #FFFF00"][/TD]
[TD="bgcolor: #FFFF00"]02[/TD]
[TD="bgcolor: #FFFF00"]2015[/TD]
[TD="bgcolor: #FFFF00"]12[/TD]
[TD="bgcolor: #FFFF00"]2015[/TD]

[TD="align: center"]12[/TD]
[TD="bgcolor: #FFFF00"]M18900900000001[/TD]
[TD="bgcolor: #FFFF00"][/TD]
[TD="bgcolor: #FFFF00"][/TD]
[TD="bgcolor: #FFFF00"]02[/TD]
[TD="bgcolor: #FFFF00"]2015[/TD]
[TD="bgcolor: #FFFF00"][/TD]
[TD="bgcolor: #FFFF00"][/TD]

[TD="align: center"]13[/TD]
[TD="bgcolor: #FFFF00"]M18900900000001[/TD]
[TD="bgcolor: #FFFF00"][/TD]
[TD="bgcolor: #FFFF00"][/TD]
[TD="bgcolor: #FFFF00"]02[/TD]
[TD="bgcolor: #FFFF00"]2015[/TD]
[TD="bgcolor: #FFFF00"][/TD]
[TD="bgcolor: #FFFF00"][/TD]

[TD="align: center"]14[/TD]

[TD="align: center"]15[/TD]

[TD="align: center"]16[/TD]

[TD="align: center"]17[/TD]
[TD="bgcolor: #FFFF00"]M18912170000009[/TD]
[TD="bgcolor: #FFFF00"][/TD]
[TD="bgcolor: #FFFF00"][/TD]
[TD="bgcolor: #FFFF00"]04[/TD]
[TD="bgcolor: #FFFF00"]2015[/TD]
[TD="bgcolor: #FFFF00"]04[/TD]
[TD="bgcolor: #FFFF00"]2017[/TD]

[TD="align: center"]18[/TD]
[TD="bgcolor: #FFFF00"]M18912170000009[/TD]
[TD="bgcolor: #FFFF00"][/TD]
[TD="bgcolor: #FFFF00"][/TD]
[TD="bgcolor: #FFFF00"]04[/TD]
[TD="bgcolor: #FFFF00"]2016[/TD]
[TD="bgcolor: #FFFF00"]04[/TD]
[TD="bgcolor: #FFFF00"]2017[/TD]

[TD="align: center"]19[/TD]
[TD="bgcolor: #00B0F0"]M18912170000002[/TD]
[TD="bgcolor: #00B0F0"][/TD]
[TD="bgcolor: #00B0F0"][/TD]
[TD="bgcolor: #00B0F0"]02[/TD]
[TD="bgcolor: #00B0F0"]2015[/TD]
[TD="bgcolor: #00B0F0"]12[/TD]
[TD="bgcolor: #00B0F0"]2015[/TD]

[TD="align: center"]20[/TD]
[TD="bgcolor: #00B0F0"]M18912170000002[/TD]
[TD="bgcolor: #00B0F0"][/TD]
[TD="bgcolor: #00B0F0"][/TD]
[TD="bgcolor: #00B0F0"]01[/TD]
[TD="bgcolor: #00B0F0"]2016[/TD]
[TD="bgcolor: #00B0F0"]12[/TD]
[TD="bgcolor: #00B0F0"]2016[/TD]

[TD="align: center"]21[/TD]
[TD="bgcolor: #00B0F0"]M18912170000002[/TD]
[TD="bgcolor: #00B0F0"][/TD]
[TD="bgcolor: #00B0F0"][/TD]
[TD="bgcolor: #00B0F0"]01[/TD]
[TD="bgcolor: #00B0F0"]2017[/TD]
[TD="bgcolor: #00B0F0"][/TD]
[TD="bgcolor: #00B0F0"][/TD]

[TD="align: center"]22[/TD]

[TD="align: center"]23[/TD]

[TD="align: center"]24[/TD]

</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]

[TABLE="class: cms_table"]
<tbody>[TR]
[TD]M10306960000001[/TD]
[TD][/TD]
[TD][/TD]
[TD]03[/TD]
[TD]1994[/TD]
[TD]12[/TD]
[TD]1997[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="bgcolor: #FFFF00"]M10306960000002[/TD]
[TD="bgcolor: #FFFF00"][/TD]
[TD="bgcolor: #FFFF00"][/TD]
[TD="bgcolor: #FFFF00"]03[/TD]
[TD="bgcolor: #FFFF00"]1994[/TD]
[TD="bgcolor: #FFFF00"]12[/TD]
[TD="bgcolor: #FFFF00"]1995

is the 001 the same as 002[/TD]
[/TR]
</tbody>[/TABLE]

 

[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

[TABLE="width: 327"]
<colgroup><col><col><col span="3"></colgroup><tbody>[TR]
[TD]id[/TD]
[TD]date[/TD]
[TD]amount[/TD]
[TD]lower[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]01/04/2018[/TD]
[TD="align: right"]27[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]02/04/2018[/TD]
[TD="align: right"]31[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]03/04/2018[/TD]
[TD="align: right"]35[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]04/04/2018[/TD]
[TD="align: right"]39[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]05/04/2018[/TD]
[TD="align: right"]43[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]01/04/2018[/TD]
[TD="align: right"]47[/TD]
[TD]L[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]07/04/2018[/TD]
[TD="align: right"]51[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]08/04/2018[/TD]
[TD="align: right"]55[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]09/04/2018[/TD]
[TD="align: right"]59[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]10/04/2018[/TD]
[TD="align: right"]63[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]11/04/2018[/TD]
[TD="align: right"]67[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]12/04/2018[/TD]
[TD="align: right"]71[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]08/04/2018[/TD]
[TD="align: right"]75[/TD]
[TD]L[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]14/04/2018[/TD]
[TD="align: right"]79[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]15/04/2018[/TD]
[TD="align: right"]83[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]16/04/2018[/TD]
[TD="align: right"]87[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]is this correct[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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