Change cell fill when changed different color depending on the day

**rev0jace** · 11-14-2012, 02:34 PM

First off hello and I hope I am posting this in the correct section.

I have a new spreadsheet that is issued daily (Mon-Fri) in which I need to update the cells in which the milestone is completed. I in turn need to highlight the cell I edited so that it stands out when another department takes those dates and transfers them to our customers database. Quite frankly the way we are doing this is more than antiquated but this is how I am required to do it.

Monday - Yellow
Tuesday – Green
Wednesday – Blue
Thursday – Purple
Friday - PINK

Example, if I actualize MS94.2 for Project blah blah today then MS94.2 in that cell would be 11/14 filled in Blue

Untitled-1.jpg

Thanks in advance,
JH

**6StringJazzer** · 11-14-2012, 10:49 PM

Welcome to the Forum!

I am trying to understand what your process is. Is this right:

Each day you get a workbook with no color fill. You fill in some of the blank cells with completion dates. You want cells with those new completion dates to be colored according to your list.

Can the date you enter be any date? Or is it always updated to show what was completed the previous day? How does the coloring get cleared out for the next day? I am hoping this can be done with conditional formatting, but if not it is a straightforward problem to solve with VBA code.

Also please see my signature regarding attaching images. I suggest you attach your file if it does not contain sensitive data.

**rev0jace** · 11-15-2012, 09:33 AM

Thank you for the response.

Correct no fill in any cell, date can be today to 10 days in the past and unfortunately conditional formatting will not work as would have to readd to the sheet daily. I figure that if this can be done it would be a huge ordeal but figured I would ask.

Thank you for the info regarding attachments. Attached is the file with sensitive info removed.

111412_ ILWI_ETTCS CP001.xls

**6StringJazzer** · 11-15-2012, 11:35 PM

I can't tell which columns you are allowed to enter dates in, so this [rev-jce=color date entries= 111412_ ILWI_ETTCS CP001.xlsm] covers any of them where a date is entered. I added another sheet to define the color codes. You can update this sheet if you want to change the colors that are used. If the extra sheet is not an option let me know and we can hard-code the colors. This sets color anytime you enter a value that is a valid date. If you change a colored cell to a value that is not a valid date or delete the value, then it clears the color. If you want to clear all the colors you need to do that manually. I could add a button for that if you want.