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

Dazzawm

Dazzawm

Well-known Member
Joined
Jan 24, 2011
Messages
3,786
Office Version
  1. 365
Platform
  1. Windows
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
KLMNOPQ
IdentifierSMSYEMEY
M10306960000001031994121997
M10306960000001031994121997
M10306960000001031994121997
M18912550000000062016122016
M18912550000000062016122016
M18912550000000062016122016
M18900900000002022015
M18900900000002022015
M18900900000002022015
M18912170000007052016
M18912170000007052016
M18912170000007052016

<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.
 
Last edited:
Sort by date Sort by votes
wideboydixon said:
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
Click to expand...
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.
 
Upvote 0

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Give this a shot:

Code: 
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
 
Upvote 0
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
NOPQ
041998021998

<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>
[TD="align: center"]5037[/TD]

</tbody>
Sheet1​
 
Last edited:
Upvote 0
I haven't tested this:

Code: 
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
 
Upvote 0
Thanks mate I will try tomorrow when back in work.
 
Upvote 0
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
 
Upvote 0
Ewww. When it errors can you press Ctrl+G to go to the immediate window and type the following and hit [Enter]:

Code: 
? thisRow

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

WBD
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Solved
Age Group Ranging by gender
Replies
2
Views
275
Raj1979
R
M
  • Question Question
HELP IDENTIFY ORIGINAL START DATE BASED ON CONTINOUS SERVICE RULES
Replies
0
Views
570
mrshuck
M
airforceone
  • Solved
Search Row 1 for Text if Found Convert Column to Date
2
Replies
11
Views
1K
airforceone
airforceone
Dazzawm
  • Question Question
Macro To Highlight Invalid Dates
2
Replies
16
Views
864
mikerickson
mikerickson
P
  • Solved
Computing investment return formula
Replies
8
Views
682
polarwoc
P

Forum statistics

Threads
1,224,836
Messages
6,181,252
Members
453,028
Latest member
letswriteafairytale

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top