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.

 

[wideboydixon]

So what values are in the cells N6560:Q6560 i.e. on row 6560 in the sheet?

WBD

 

[Dazzawm]

N is 12 O is 2011 P & Q are blank

 

[wideboydixon]

If P6560 was truly blank then that line of code wouldn't get executed:

    If Cells(thisRow, "P").Value <> "" Then

This is a more defensive option for dealing with non-numeric cells:

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
            ' Make sure we have numeric values
            If IsNumeric(Cells(thisRow, "O").Value) _
            And IsNumeric(Cells(thisRow, "N").Value) _
            And IsNumeric(Cells(thisRow - 1, "Q").Value) _
            And IsNumeric(Cells(thisRow - 1, "P").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
        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 IsNumeric(Cells(thisRow, "N").Value) _
    And IsNumeric(Cells(thisRow, "O").Value) _
    And IsNumeric(Cells(thisRow, "P").Value) _
    And IsNumeric(Cells(thisRow, "Q").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]

Unfortunately now that highlights all rows without an end date in P & Q? In my very first post rows 14, 15 & 16 are being highlighted when they should be left clear as there is nothing wrong with them.

 

[wideboydixon]

OK. *Sigh*. It's hard sailing blind without having the actual data to hand. One last try:

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
            ' Make sure we have numeric values
            If Trim(Cells(thisRow, "O").Value) <> "" _
            And Trim(Cells(thisRow, "N").Value) <> "" _
            And Trim(Cells(thisRow - 1, "Q").Value) <> "" _
            And Trim(Cells(thisRow - 1, "P").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
        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 Trim(Cells(thisRow, "N").Value) <> "" _
    And Trim(Cells(thisRow, "O").Value) <> "" _
    And Trim(Cells(thisRow, "P").Value) <> "" _
    And Trim(Cells(thisRow, "Q").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]

Success! Its only highlighted what I expected it to, I will try on more problems and let you know. Thank you so much for your time WBD.