Improve code for excel button

Inuniform

New Member
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:
Code:
``````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``````

Inuniform

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
you are checking if the date in "F" & I is greater than today's date but your code compares "F"&i and is checking if it is less than today's date

also you don't need to compare it to a cell with today's date just use date

Code:
``If Range("F" & i) > date Then``

Sorry I have written it wrong in the in my initial question. It should be that if the current date exceeds the due date, then the status in column D should change to behind. However, I have tried your code as in
Code:
``If Range("F" & i) < date Then``
but now it changes all statusses in column D to behind, even if todays date does not exceed the due date. It does only take the the rows in which something is written though so that problem is solved.

can I see an example of your data?

Also why is your loop from 13 to 40? You take the time to set lr to the last row so why not 13 to lr?

Also you were correct on the signage and I was wrong. you had it correct from the beginning. This is what happens before coffee.

Last edited:
Allright so if I do
Code:
``If Range("F" & i) < Date Then``
then it changes the status in all rows where no dates are to behind, and if i use
Code:
``If Range("F" & i) > Date Then``
it changes everything to behind if there is a date. I really don't understand why...

Haha no worries, take your time and enjoy your coffee. allright here it comes:
 Row Nr 18 ID Task Affects the output Status Priority Due Date Requested by Start Date Assigned to: Estimated time (h) Category Current date Actual time 1 Test yes Not Started High 15/11/2016 Person x 17-Nov-16 Person y 01/12/2016 12 test no Behind High 12-Dec-16 Person x 18-Nov-16 Person y 2.00 01/12/2016 15 test no In progress High 24/11/2016 Person x 23-Nov-16 Person y 2.00 01/12/2016 17 test no Behind High 24/11/2016 Person x 23-Nov-16 Person y 2.00 Work assignment 01/12/2016 18 Mosselen no Behind High 03/12/2016 Person x 1-Dec-16 Person y 2.00 Work assignment 01/12/2016

<tbody>
</tbody>

The reason why I have 13 to 40 is because last time it changed all rows to behind and excel jammed. If the code works and only changes the statusses to behind when there is an actual task in the row, then it could be changed.

Last edited:
Allright I figured it out! I changed the code to:
Code:
``````Sub Button2()
Dim lr As Long, i As Long
Dim Tmp As Date
lr = Range("F" & Rows.Count).End(xlUp).Row
For i = 13 To 40
Tmp = CDate(Range("F" & i).Value)
If Format(Range("F" & i).Value, "yyyy-mm-dd") < Format(Date, "yyyy-mm-dd") And Range("F" & i).Value <> "" Then
Range("D" & i) = "Behind"
End If
Next i
End Sub``````

And now it works

Thanks for your effort! Have a great day!

Allright I figured it out! I changed the code to:
Code:
``````Sub Button2()
Dim lr As Long, i As Long
Dim Tmp As Date
lr = Range("F" & Rows.Count).End(xlUp).Row
For i = 13 To 40
Tmp = CDate(Range("F" & i).Value)
If Format(Range("F" & i).Value, "yyyy-mm-dd") < Format(Date, "yyyy-mm-dd") And Range("F" & i).Value <> "" Then
Range("D" & i) = "Behind"
End If
Next i
End Sub``````

And now it works

Thanks for your effort! Have a great day!

All of the line items should be behind except the last one. that said, I changed the status for each line item to either not started or in progress. They changed to behind. Changed them back and made all dates to fall in 2017, they did not change. Add more lines and changed all status to not started and randomly set some to 2015, 2016, and 2017. All dates prior to 12/01/2016 (today's date) changed to behind. All others remained the same. Here is your code with an IF statement to see if the cell in Column A has data (if not do nothing, else rest of your code), Using your lr variable to find the last row of data and finally also using the date function rather than a cell.

Code:
``````Sub Button2()
Dim lr As Long, i As Long
lr = Range("F" & Rows.Count).End(xlUp).Row
For i = 13 To lr
If Not Range("A" & i).Value = "" Then
If Range("F" & i) < Date Then
Range("D" & i) = "Behind"
End If
End If
Next i
End Sub``````

TLDR
your code works as intended on my machine using your data and further testing with dummy data.

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!

Nevermind it is already working! Everything is working fine in my workbook now. Thank you so much!

Replies
8
Views
1K
Replies
4
Views
412
Replies
5
Views
501
Replies
5
Views
471
Replies
14
Views
503

1,196,414
Messages
6,015,127
Members
441,874
Latest member
saustark

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.

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

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