A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

autofill colour based on date entered



 
 
Thread Tools Display Modes
  #1  
Old December 10th, 2008, 12:38 AM posted to microsoft.public.excel.misc
Tacrier
external usenet poster
 
Posts: 42
Default autofill colour based on date entered

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? :P

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!!
  #2  
Old December 10th, 2008, 01:16 AM posted to microsoft.public.excel.misc
Sheeloo[_3_]
external usenet poster
 
Posts: 1,713
Default autofill colour based on date entered

Your names should be in A4-A100, right?

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

Second Conditon
=B$3B4
Format BLUE

Third Condition
=B4=""
Format Grey

"Tacrier" wrote:

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? :P

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!!

  #3  
Old December 17th, 2008, 05:48 PM posted to microsoft.public.excel.misc
Tacrier
external usenet poster
 
Posts: 42
Default autofill colour based on date entered

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" wrote:

Your names should be in A4-A100, right?

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

Second Conditon
=B$3B4
Format BLUE

Third Condition
=B4=""
Format Grey

"Tacrier" wrote:

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? :P

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!!

  #4  
Old December 17th, 2008, 06:29 PM posted to microsoft.public.excel.misc
Sheeloo[_3_]
external usenet poster
 
Posts: 1,713
Default autofill colour based on date entered

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

=AND(B$2B4,C$2B4)
Format PINK

Second Conditon

=C$2B4
Format BLUE

Third Condition
=B4=""
Format Grey


Let me know what happens...


"Tacrier" wrote:

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" wrote:

Your names should be in A4-A100, right?

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

Second Conditon
=B$3B4
Format BLUE

Third Condition
=B4=""
Format Grey

"Tacrier" wrote:

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? :P

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!!

  #5  
Old December 17th, 2008, 07:02 PM posted to microsoft.public.excel.misc
Tacrier
external usenet poster
 
Posts: 42
Default autofill colour based on date entered

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?



"Sheeloo" wrote:

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

=AND(B$2B4,C$2B4)
Format PINK

Second Conditon

=C$2B4
Format BLUE

Third Condition
=B4=""
Format Grey


Let me know what happens...


"Tacrier" wrote:

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" wrote:

Your names should be in A4-A100, right?

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

Second Conditon
=B$3B4
Format BLUE

Third Condition
=B4=""
Format Grey

"Tacrier" wrote:

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? :P

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!!

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 01:47 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.