Application.Match then Compare Dates from Offset Cells

[Norske77]

Help! Please!
I have 3 sheets but only working through Sheet2 and Sheet3 for this code. The Sheet3 contains raw data that I want to validate against Sheet2, which has valid values. So basically go row by row through Sheet3 and compare the value from the identified cell against all data in the specified column in Sheet2. When a match is found I want the cells offset from the matching value in Sheet3 and Sheet2 set as the PEDATE and CEDate. I then want to compare the dates and if PEDate is greater than the CEDate I want the individual cell highlighted on Sheet3.

I am not getting an error but also not getting my desired result. Not sure if I am close or way off. Any help would be greatly appreciated.

Sub Coverage_Effective_Date()    
    Dim U As Long
    Dim C As Long
    Dim PEDate As Date
    Dim CEDate As Date
    
    With Sheet3
    
    Range("U:U").Font.ColorIndex = 1
        
        For U = 2 To Sheet3.Cells(.Rows.Count, "A").End(xlUp).Row
        For C = 3 To Sheet2.Cells(.Rows.Count, "A").End(xlUp).Row
            
            If Not IsError(Application.Match(Sheet3.Cells(U, "W"), Sheet2.Cells(C, "B"), 0)) Then
        
            PEDate = .Cells(C, "C")
            CEDate = .Cells(U, "U")
                        
            End If
            
            If PEDate > CEDate Then
            
                Sheet3.Cells(U, "U").Font.ColorIndex = 3
                
            End If
                                 
        Next C
        Next U
        
    End With
    
End Sub

 

[DanteAmor]

You help me understand what you need.
On sheet3 in which columns are the value to be compared and the date:
Sheet3-Value column =?
Sheet3-Date column =?


On sheet2 in which columns are the value to be compared and the date:
Sheet2-Value column =?
Sheet2-Date column =?


Another doubt:
If Sheet3-Date> Sheet2-Date then
highlight Sheet3-Column "U"?

 

[DanteAmor]

Another question, the data of the sheet3, can appear several times in the sheet2?

 

[Norske77]

Thanks for the quick reply.

Please see answers to your questions below.

On sheet3 in which columns are the value to be compared and the date:
Sheet3-Value column = W
Sheet3-Date column = U


On sheet2 in which columns are the value to be compared and the date:
Sheet2-Value column = B
Sheet2-Date column = C


Another doubt: Updated > to <
If Sheet3-Date < Sheet2-Date then
highlight Sheet3-Column "U". This is correct.

Correct - The data on sheet3, can appear several times in the sheet2.

 

[DanteAmor]

Thanks for the quick reply.

Please see answers to your questions below.

On sheet3 in which columns are the value to be compared and the date:
Sheet3-Value column = W
Sheet3-Date column = U

On sheet2 in which columns are the value to be compared and the date:
Sheet2-Value column = B
Sheet2-Date column = C

Another doubt: Updated > to <
If Sheet3-Date < Sheet2-Date then
highlight Sheet3-Column "U". This is correct.

Correct - The data on sheet3, can appear several times in the sheet2.

Try this

The macro gets the maximum date from sheet2 and compares it to the date on sheet3, if sheet3-date <sheet2-date then highlight

Sub Coverage_Effective_Date()
    Dim c As Range, r As Range, wmax As Variant
    Set r = Sheet2.Range("B2:B" & Sheet2.Range("B" & Rows.Count).End(xlUp).Row)
    Sheet3.Range("U:U").Font.ColorIndex = 1
    For Each c In Sheet3.Range("W2", Sheet3.Range("W" & Rows.Count).End(xlUp))
      wmax = Evaluate("=sum(max(('" & Sheet2.Name & "'!" & r.Address _
        & "=""" & c.Value & """)*('" & Sheet2.Name & "'!" & r.Offset(, 1).Address & ")))")
      If Sheet3.Cells(c.Row, "U").Value < wmax Then Sheet3.Cells(c.Row, "U").Font.ColorIndex = 3
    Next
End Sub

I put my examples

Sheet2
<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:26.61px;" /><col style="width:43.72px;" /><col style="width:98.85px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td > </td><td >data1</td><td style="text-align:right; ">14-jul</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td > </td><td >data1</td><td style="text-align:right; ">18-jul</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td > </td><td >data1</td><td style="text-align:right; ">15-jul</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td > </td><td >data2</td><td style="text-align:right; ">14-jul</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td > </td><td >data2</td><td style="text-align:right; ">18-jul</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td > </td><td >data2</td><td style="text-align:right; ">15-jul</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td > </td><td >data3</td><td style="text-align:right; ">14-jul</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td > </td><td >data3</td><td style="text-align:right; ">18-jul</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td > </td><td >data3</td><td style="text-align:right; ">15-jul</td></tr></table>

---------------------
Sheet3
<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:30.42px;" /><col style="width:30.42px;" /><col style="width:39.92px;" /><col style="width:30.42px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >T</td><td >U</td><td >V</td><td >W</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td > </td><td > </td><td style="color:#ff0000; text-align:right; ">10-jul</td><td > </td><td >data1</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td > </td><td > </td><td style="text-align:right; ">31-jul</td><td > </td><td >data2</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td > </td><td > </td><td style="color:#ff0000; text-align:right; ">15-jul</td><td > </td><td >data3</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr></table>

 

[Norske77]

The process is slightly different than what you laid out.

Sheet2 has one valid date for each value. Each value can only be listed once.

Policy#/Plan Type Policy Effective Date
1234567 High 01/01/2019
1234567 Mid 01/01/2019
1234567 Low 01/01/2019



Sheet 3 has multiple iterations of the valid value. Any date on Sheet3 that is before the date for the corresponding value on Sheet2 should be highlight in red.

Coverage Effective Date QLE/Revised Eligibility Date Plan Type
01/01/2018 1234567 High
01/01/2018 1234567 Mid
01/01/2019 1234567 Low
01/01/2020 1234567 Mid
01/01/2018 1234567 High
01/01/2018 1234567 Mid
01/01/2018 1234567 High
01/01/2018 1234567 Low
01/01/2019 1234567 High
01/01/2019 1234567 Mid
01/01/2019 1234567 Low
01/01/2019 1234567 Mid
01/01/2019 1234567 High

 

[Norske77]

Sorry could not figure out how to post the data in the spreadsheet form and it got jumbled. Hopefully it is still clear.

 

[DanteAmor]

Then I don't understand what you need.
Forget the code, forget my example.


Then you could put an example with images that clearly show your data from sheet2 and sheet3; and in a third image the result you expect.

You could upload a copy of your file to a free site such www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

in the file explain the result you need using the sample data that is in the same file.