Improve code for excel button

[Inuniform]

Hi everybody,

I was hoping you could help me with the following problem. Currently I am working on a register system and for that I have a button that if the current date (J9 with =Today function) exceeds the due date of a particular row in Column F that the status in column D changes to "Behind". The problem is that the code changes all statusses to behind in column D, even if a row has no content at all. Is there a possibility to change the code so it only changes the statuses to "behind" if there is something written in for example column A? The code I use is the following:

Sub Button2()
    Dim lr As Long, i As Long
    lr = Range("F" & Rows.Count).End(xlUp).Row
    For i = 13 To 40
        If Range("F" & i) < Range("J9") Then
            Range("D" & i) = "Behind"
        End If
    Next i
End Sub

Thanks in advance!

Inuniform

 

[RCBricker]

yeah I saw your its working post after I posted last time. glad it is working

 

[RCBricker]

Thanks alot! I have tried your code and it works perfectly. I have made a new thread for this but since you are familiar with my data you might be able to answer this. (I can imagine that I'm not supposed to make a new thread since it is almost the same question and you are already helping me, but I am working with a deadline and you were offline so I was stressing a bit ) As you know In column A every task gets an ID tag. I have made a control panel where people can type in an ID in K4 and when they push the button the status in column D for the row that matches the ID in column A should change to "Completed".

If you could help me out with this one too you are my hero of the year 2016!

you got this new button working?

just in case you didn't and you still need it.

Sub button3()
Dim rng As Range, cell As Range
Dim lngrow As Long, lngcell As Long
Dim ws As Worksheet
Dim vENTRY As Variant

    Set ws = ActiveSheet
    Set cell = ws.Range("K4")
    vENTRY = cell.Value
    If Not vENTRY = "" Then
        lngrow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
        Set rng = ws.Range(ws.cells(13, 1), ws.cells(lngrow, 1))
        
        lngcell = rng.Find(vENTRY).Row
        ws.cells(lngcell, 4).Value = "Completed"
    End If
End Sub