Need help with formatting a cell which uses a drop down to display status when the date is older than today

[Kyle1993]

Hi All

I really need your help.

I have an inventory in and out log. It logs date an asset was issued and date it was returned. I have everything in a drop down menu and i need the following. these assets are loaned out on a daily basis and returned on a daily basis. (Or supposed to be) there are other assets issued on a semi permanent basis
My drop down items on another sheet are (data validation):
Issued (Conditional Formatting Turns the Cell Yellow if issued is selected)
Returned (Conditional Formatting turns the cell Green if returned is selected)
Semi-Permanent (Conditional Formatting turns the cell orange if semi-permanent is selected)
Lost (turns red when selected)
Stolen (turns purple when selected)

If the cell with the date issued is today, the cell containing the selected status "Issued" turns yellow. Issued is a drop down item on another sheet.
If the cell with the date issued is older than today and the status of the asset still says issued I want the cell to change from yellow fill saying Issued, to Red fill and say "OVERDUE" in bold white. OVERDUE is not a drop down item on another sheet.
Essentially every night at midnight i want the status of the assent issued.

Perhaps i should add OVERDUE to the drop down items but im really not sure

 

[6StringJazzer]

You can't allow a user to enter data and also have a formula to change it to OVERDUE both in the same cell. That means you need a VBA solution. You will need to add OVERDUE to the data validation list to allow that value to be set by the code.

The code would have to be specific to where your data is located. If you provide more information, perhaps a screen shot showing your dropdown cells, your dates, and row and column headers, we could get started.