Complex VBA problem auto insert date and time, auto insert comment, etc.

Hi,

I'm hoping someone can help me.

Attached is my excel file. A brief background of my excel file. I work in a hotel. When a guest fails to check-in on his reservation, we want to put it in a log. The details are the date it was logged (Column D), the room number (Column E), Surname (Column F), First Name (Column G), Company Name (Column H), Check-in DAte (Column I) and the Check-Out Date (Column J), Booking No (Column K). Two departments will act on it. First, is the Reservations Department, they will find out why the guest failed to check in. They will put the reason on the Reservation Notes column (Column L), Reservation Agent who actioned it (Column M), if it is a hotel or guest error (Column N), amount to be charged (Column O), whether we will charge or not (Column P) and whether it has been resolved or not (Column Q). If reservations department has resolved it, then it is Front Office turn to action it. They will put action taken under the Front Desk Notes column (Column R), Front Desk Agent's nae (Column S), and whether it has been resolved or not (Column T). The last column (Column U) is to denote the over all status of the reservation whether it has been sorted (Close) or still needs to be action (Pending). Here is what I'm hoping to achieve.

1. Under the Reservations Department Section, the reservations agent will put her notes in the Reservations Notes (Column L), when something still needs to be done, they will select "No" in the Resolved? (Column Q). Ex. Column L: "advised travel agent we will charge 1 night. verify if charge to the credit card on file. Travel agent will contact guest.Awaiting call back from travel agent" ; Column Q: "No". So when the travel agent calls back and say to charge it to the card on file, the reservation agent will update the log with add-on comments to the Column L. Ex. "Travel agent called back and said to charge it to the credit card on file" and change the Column Q to "Yes".

I am hoping that the first time the Reservations Agent added notes to the Column L, it will automatically log the date (dd:mm) and time (hh:mm AM). When the reservation agent updated the Column Q from "No" to "Yes", a pop-up box will appear for the reservation agent to put the add-on comment "Travel agent called back and said to charge it to the credit card on file" and the message will be place in Column L just below the previous notes with the new date and time logged.

The date and time of the logged should not change everytime it is being updated.

Ex: Column L

22.06 8:51 AM advised travel agent we will charge 1 night. verify if charge to the credit card on file. Travel agent will contact guest.Awaiting call back from travel agent
23:06 12:00 PM Travel agent called back and said to charge it to the credit card on file


2. Same request for the Front Desk Section. but instead of Column L it is Column R and instead of Column Q it is Column T.


3. When Column Q and Column T is "No", when "Pending" is selected in Column Q, it will highlight the row color RED
When Column Q and Column T is "Yes", when "Close" is selected, it will grade out the entire row.
When Column Q is "Yes" and Column T is "No", when "Pending" is selected it will highlight the row with color RED but the font of the letters for the Front office section (Column R, S, T) will be bold and different font color so that it will show which department needs to act on it. Vice Versa.
When Column Q is "Yes" and Column T is "No", when "Close" is selected, an error message will pop-up that will state "Not yet resolved. Still needs to be actioned. Please review."



No Shows updated.xlsm

Up! Need help with this.