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

Dazzawm

Dazzawm

Well-known Member
Joined
Jan 24, 2011
Messages
3,783
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

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
If P6560 was truly blank then that line of code wouldn't get executed:

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

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

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
            ' 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
 
Upvote 0
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.
 
Upvote 0
OK. *Sigh*. It's hard sailing blind without having the actual data to hand. One last try:

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
            ' 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
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Solved
Age Group Ranging by gender
Replies
2
Views
259
Raj1979
R
M
  • Question Question
HELP IDENTIFY ORIGINAL START DATE BASED ON CONTINOUS SERVICE RULES
Replies
0
Views
552
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
852
mikerickson
mikerickson
P
  • Solved
Computing investment return formula
Replies
8
Views
674
polarwoc
P

Forum statistics

Threads
1,223,911
Messages
6,175,326
Members
452,635
Latest member
laura12345

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