Adding a static date stamp w/fill color Hello Through the use of EXCEL, we have

Question

Adding a static date stamp w/fill color

Hello

Through the use of EXCEL, we have a multi-tab, multi-step workflow spreadsheet used to track progress on tasks and capture when complete. Column G is our 'completion status' column. When someone reports the task done, we mark that appropriate row/column as complete. Previously, we just used 'COMPLETE' and I had conditional formatting to turn it green. Now, the team wants to instead have it stamped with a date / time and also turn it green.

The date needs to stay static after being implemented; because the workflow lasts about 20 weeks, the date range is very wide. Once a task is complete, I cannot have it change when I open the spreadsheet the next day, or next week or next month.

So, I've figured out how to use VBA to put in the static date. I created a macro called =COM. In VBA I set a macro called =DateandTime() which states the following:

Function DateAndTime()
DateAndTime = Now
End Function

Works great for putting the date. In the 'completion status' column, I just type in =com, hit return and I get the appropriate date. I saved and opened and it does not change.

My challenge is how to get it to turn green. I've tried conditional formatting; I've tried adding a selection.interior,colorindex command to the function but I do not know the proper syntax.

I am hoping someone can help.  

Thoughts?

Explanation / Answer

99 H i,

You lost me at the bit where you said you type =com and hit return and get the date but if that works for you then fine. You can still use conditional formatting to turn the cell green like this:-

Select G2 then drag to select as many cells as you want then

Home tab | Conditional formatting | New rule | use a formula to decide which cells to format and enter this formula

=len(g2)>0

Click the format button | Fill tab and choose a green colour and OK out.

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.