Changing cell colours depending on when they were entered.

[misterg]

Interesting situation.

I have a worksheet that will, over the course of one week, be filled with data...e.g.some information recorded on monday, some on tuesday and so forth.

how can i differentiate between what data was filled in on any day of the week?

I have one cell with a Now() formula in and one other cell where the user types in the date of the Week Commencing.

Does a formula exist whereby the background of the cell or even the colour of the font will change depending on what number day of the week (Monday = 1 etc) the cell data was entered?

Is that possible?

Many thanks

G

[Jack in the UK]

OK would a script that allows the whole line to be coloured to 5 or 7 colours be ok, what happens next week.. ???

this would be linked to command button

[misterg]

Jack,

At the end of the week, the user emails the form (using a command button) to a team leader.

Incorporated in this button action is a facility to clear the selected cells and save the workbook over the original file.

Basically, the user starts fresh every week with blank cells (the completed spreadsheet resides in the users sent items box and the recipients email folder).

So in effect, the original file, at the end of every week, will end up blank.

Does that help(i have the code for that, its just getting the teamleader to easily see what data was eneterd in on which day).

try that on for size!

G

[Corni]

With the code below you can control font color of all input based on the day of the week when the update is performed:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Select Case Weekday(Date)
Case 0
Target.Font.ColorIndex = 3
Case 1
Target.Font.ColorIndex = 4
Case 2
Target.Font.ColorIndex = 5
Case 3
Target.Font.ColorIndex = 6
Case 4
Target.Font.ColorIndex = 7
Case 5
Target.Font.ColorIndex = 8
Case 6
Target.Font.ColorIndex = 9
End Select
Application.EnableEvents = True
End Sub