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]

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

In the first example you refer to all 3 rows have the same dates, sorry should have said. Yes and when an error is found within that group then I would like them highlighted.

 

[oldbrewer]

sorry - I cannot spend more time on this.....

 

[Dazzawm]

sorry - I cannot spend more time on this.....

Thanks for your time, hopefully wideboydixon will come up with a solution.

 

[wideboydixon]

Give this a shot:

Public Sub CheckSequentialDates()

Dim lastRow As Long
Dim thisRow As Long
Dim firstRow As Long
Dim inError As Boolean

lastRow = Cells(Rows.Count, "C").End(xlUp).Row + 1
inError = False
For thisRow = 2 To lastRow
    If Cells(thisRow, "C").Value = Cells(thisRow - 1, "C").Value Then
        If Cells(thisRow, "N").Value <> Cells(thisRow - 1, "N").Value _
        Or Cells(thisRow, "O").Value <> Cells(thisRow - 1, "O").Value _
        Or Cells(thisRow, "P").Value <> Cells(thisRow - 1, "P").Value _
        Or Cells(thisRow, "Q").Value <> Cells(thisRow - 1, "Q").Value Then
            If DateSerial(Cells(thisRow, "O").Value, Cells(thisRow, "N").Value, 1) < DateSerial(Cells(thisRow - 1, "Q").Value, Cells(thisRow - 1, "P").Value, 1) Then
                inError = True
            End If
        End If
    Else
        If inError Then Range(Cells(firstRow, "C"), Cells(thisRow - 1, "Q")).Interior.Color = vbYellow
        inError = False
        firstRow = thisRow
    End If
Next thisRow

End Sub

WBD

 

[Dazzawm]

Thanks WBD, works perfect. I have come across another problem if it can be added in. I have come across errors where the end month and end year are before the start month and start year like below.

Excel 2010

	N	O	P	Q
5037	04	1998	02	1998

<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></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

 

[wideboydixon]

I haven't tested this:

Public Sub CheckSequentialDates()

Dim lastRow As Long
Dim thisRow As Long
Dim firstRow As Long
Dim inError As Boolean

' Get the last row of data
lastRow = Cells(Rows.Count, "C").End(xlUp).Row + 1

' Set up other variables
inError = False
firstRow = 1

' Process all rows
For thisRow = 2 To lastRow
    ' Is this the same identifier as the last row?
    If Cells(thisRow, "C").Value = Cells(thisRow - 1, "C").Value Then
        ' If the dates are the same as the last row then there's nothing to do
        If Cells(thisRow, "N").Value <> Cells(thisRow - 1, "N").Value _
        Or Cells(thisRow, "O").Value <> Cells(thisRow - 1, "O").Value _
        Or Cells(thisRow, "P").Value <> Cells(thisRow - 1, "P").Value _
        Or Cells(thisRow, "Q").Value <> Cells(thisRow - 1, "Q").Value Then
            ' Is the start date before the previous end date?
            If DateSerial(Cells(thisRow, "O").Value, Cells(thisRow, "N").Value, 1) < DateSerial(Cells(thisRow - 1, "Q").Value, Cells(thisRow - 1, "P").Value, 1) Then
                ' Flag this group as being in error
                inError = True
            End If
        End If
    Else
        ' Highlight the group if there's been an error
        If inError Then Range(Cells(firstRow, "C"), Cells(thisRow - 1, "Q")).Interior.Color = vbYellow
        
        ' The new group is not in error - remember the row it started on
        inError = False
        firstRow = thisRow
    End If
    
    ' Check for invalid dates on this row
    If Cells(thisRow, "P").Value <> "" Then
        If DateSerial(Cells(thisRow, "O").Value, Cells(thisRow, "N").Value, 1) > DateSerial(Cells(thisRow, "Q").Value, Cells(thisRow, "P").Value, 1) Then
            inError = True
        End If
    End If
Next thisRow

End Sub

WBD

 

[Dazzawm]

Thanks mate I will try tomorrow when back in work.

 

[Dazzawm]

I got a run-time error '13' type mismatch when I debugged it pointed to this line.

If DateSerial(Cells(thisRow, "O").Value, Cells(thisRow, "N").Value, 1) > DateSerial(Cells(thisRow, "Q").Value, Cells(thisRow, "P").Value, 1) Then

 

[wideboydixon]

Ewww. When it errors can you press Ctrl+G to go to the immediate window and type the following and hit [Enter]:

? thisRow

Then tell me what's in column N:Q on that row of the sheet.

WBD

 

[Dazzawm]

6560 appears after I have hit enter