autofill colour based on date entered

[Tacrier]

Hi there!

I had posted this question last week and didn't get far without the analysis
add-in. I got it now but am missing the instructions on the howto?

What I would like to do is colour code a spreadsheet for work handedin by
staff.

I have the top row showing the calendar months from April 2008 to March
2009. The second row shows the due date to the SUpervisor and the due date
for review for each month like this:


Column A2:A3 = April 2008
Column B2 = May 7, 2008
Column B3 = May 14, 2008

The columns C-Z continue on like this with different due dates for each month.

Column A1:A200 = Employee Name

Cells entered with a date within the two particular due dates in a month I
would like coloured pink.

Cells entered with a date after the review due date in a month I would like
coloured blue.

Cells that have no entry I would like coloured grey to indicate missing work
not handed in.

Is this possible?

Any help is greatly appreciated.

Thanks!!

 

[Sheeloo]

Your names should be in A4-A100, right?

Select B4:Z100
Choose conditional formatting
Choose Formula Is
First condition
=AND(B$2<B4,B$3>B4)
Format PINK

Second Conditon
=B$3<B4
Format BLUE

Third Condition
=B4=""
Format Grey

 

[Tacrier]

Sorry for not replying right away,

I tried the conditional formatting you had suggested, I modified it (or
tried to) because I had put the wrong column labels when describing my
spreadsheet. Here is the correct column labels:

Column A4:A100 Name of Employee

Column B2:C2 April 2008
Column B3 May 7, 2008
Column C3 May 14, 2008
Column D2:E2 May 2008
Column D3 June 9, 2008
Column E3 June 15, 2008
Column F2:G2 July 2008
Column F3 August 5, 2008
Column G3 August 10, 2008

etc

The colour coding of cells would be in B4:Z100

Pink : For entered date that falls between the two dates for the particular
month.
Blue : For entered date that falls after the 2nd date for the particular month
Grey : For no date entered to indicate no work handed in

I hope I did not make it too confusing, any/all suggestions are greatly
appreciated.

Thanks

 

[Sheeloo]

Try

Select B4:Z100
Choose conditional formatting
Choose Formula Is
First condition

Format PINK

Second Conditon =C$2<B4
Format BLUE

Third Condition
=B4=""
Format Grey

Let me know what happens...

 

[Tacrier]

It made all of the empty cells grey (yay!) and all of the entered cells blue
(boo) lol

Don't know what else to do except manually colour the cells?