Check Date to see if it has been Reached

[basketball2524]

Hello I have the following problem.

I have dates listed in Column F. I need help creating a macro that will look through Column F and see which dates have passed or hsa been met based on the day the macro is run. If it finds a date that has passed or has been met then it will check Columns G and I and see if in that row those columns have any values. If either one of them do then the program will skip this date and go to the next date to check. If they are both empty then in the row with the current date being checked Column H receives a value of "Yes". Thanks for any help I can get on this.

[PedroSchulz]

So, as long as you name cell C8 with the =TODAY() function it should work. Does that help you or you need a function imputed inside the macro?

Sub dateval()
Dim lin As Integer
Dim valor As Double

lin = 1
valor = Cells(lin, 6)
While Cells(lin + 1, 6) <> Empty
lin = lin + 1
valor = Cells(lin, 6)
If valor = Cells(8, 3) Or valor > Cells(8, 3) Then
Cells(lin, 7) = "ok"
End If
Wend
End Sub

[PedroSchulz]

Hey, it's better this way (I suppose)

Sub dateval()
Dim lin As Integer
Dim valor As Double

lin = 1
valor = Cells(lin, 6)
While Cells(lin + 1, 6) <> Empty
lin = lin + 1
valor = Cells(lin, 6)
If valor = date Or valor > date Then
Cells(lin, 7) = "ok"
End If
Wend
End Sub

Cheers

[basketball2524]

It works I just forgot to include one thing that is making it not work correctly for me. In column F there will be spaces in between some of the rows. So i would need it to run for the whole column say up to 5000 rows.

[basketball2524]

Actually hold on let me do some more tests.

[basketball2524]

Besides for that one thing from the last post. I'm noticing that its not noticing the right columns. If the date criteria is met then its suppose to check Columns G and I to see if they have any values in them. If either one of those columns do. Then Column H wont receive anything. If both columns G and I are empty after the date criteria is met then Column H will receive a "yes".

[basketball2524]

Ok I have the following code that works for the task I needed.

Sub colorcheck3()


    Dim sOne As Worksheet
    Dim sTwo As Worksheet
    
    Set sOne = ThisWorkbook.Sheets("Sheet1") ' CHANGE ME
    Set sTwo = ThisWorkbook.Sheets("Sheet2") ' CHANGE ME
    
    Dim startRow1 As Integer
    Dim endRow1 As Long
        
    startRow1 = 1
    endRow1 = sOne.Cells(Rows.Count, "C").End(xlUp).Row
    
    Dim startRow2 As Integer
    Dim endRow2 As Long
        
    startRow2 = 12
    
    endRow2 = sTwo.Cells(Rows.Count, "A").End(xlUp).Row
    For x = startRow1 To endRow1
        If sOne.Cells(x, 3).Interior.ColorIndex <> -4142 Then
            For y = startRow2 To endRow2 Step 1
                If sOne.Cells(x, 3).Value = sTwo.Cells(y, 1) Then
                    If sTwo.Cells(y, 9).Value <> "Yes" Then
                       If Format(sTwo.Cells(y, 6).Value, "m/d/yyyy") >= Format(Now(), "m/d/yyyy") Then
                                    sTwo.Cells(y, 8) = ""
                                Else
                                    If sTwo.Cells(y, 7).Value <> "Yes" Then
                                        sTwo.Cells(y, 8) = "Yes"
                                    End If
                            End If
                        End If
                    End If
            Next y
        End If
    Next x
    
End Sub

The problem i'm running into is that in cell(y,6) I have the following formula that is pulling the date:

=IF(ISNUMBER(E14),WORKDAY(E14,2,$N$1:$P$2),"")

How can I change the code or add to it that will read the cell correctly because right now it isn't reading it as a date.

[PedroSchulz]

How many blank spaces between dates would you have? I think it's a easier way to do it

[basketball2524]

It could vary no set number.

[PedroSchulz]

Are you using excel 2007?

[basketball2524]

No I'm using Excel 2013.

[PedroSchulz]

Could you try this?

Sub dateval()
Dim lin As Integer
Dim lin2 As Long
Dim value As Double

lin = 1
lin2 = 999999
value = Cells(lin, 6)

While Cells(lin2, 6) = Empty

    lin2 = lin2 - 1

Wend

While lin <> lin2
    lin = lin + 1
    value = Cells(lin, 6)
    If value = Date Or value > Date Then
        Cells(lin, 7) = "ok"
    End If
    
Wend
    
End Sub

[basketball2524]

I'm getting an error and that could be because in Column F it is not a normal Date value. Listed above I showed the formula that I have in there to pull a certain date for me. So with the code that I have thats working mostly I'm just trying to figure out how to make it read that cell correctly instead of looking for a date value.

[PedroSchulz]

You need the formula to be in date format, right?
Does that help?

=DATE(YEAR(IF(ISNUMBER(E14),WORKDAY(E14,2,$N$1:$P$2),""));MONTH(IF(ISNUMBER(E14),WORKDAY(E14,2,$N$1:$P$2),""));DAY(IF(ISNUMBER(E14),WORKDAY(E14,2,$N$1:$P$2),"")))