Colouring cells dependent on date and owner

sing2trees1983

New Member
Joined
Feb 3, 2006
Messages
26
Hi all,

Hope someone can help me here.
I have a spreadsheet with the following column headers:
A Task
B Owner
C Start date
D End date
E Completion
F 1st Nov
G 2nd Nov
H 3rd Nov (etc)

What I would like is that if I enter a task in A, owner and the due start/end date then the relevant date range (col F onwards) would change colour from white to orange for example.

Then when a completion date is entered, the whole row turns green.

Is this possible and if so, can anyone help with the formula?

Many thanks in advance!
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Hi Welcome to MrExcel.

You need a Conditional Formatting formula like this for the Orange....
=AND($E$2="",F1>=$C$2,F1<=$D$2)

And a formula like this for the Green....
=$E$2<>""

To apply the following formulas Select the range from F1:AI1 (this assumes you have the dates from 01/11/11-30/11/11 in these cells)

In 2007, select Conditional Formatting or press Alt O D.
Click New Rule.
Click Use a Formula etc
In the white box paste the first formula provided.
Click Format, Select your colour, click Ok, Ok

Click New Rule.
Click Use a Formula etc
In the white box paste the second formula provided.
Click Format, Select your colour, click Ok, Ok

Hopefully you will end up with something like this....

Excel Workbook
ABCDEFGHIJKLM
1TaskOwnerStart dateEnd dateCompletion01 Nov 1102 Nov 1103 Nov 1104 Nov 1105 Nov 1106 Nov 1107 Nov 1108 Nov 11
2CleanBill03/11/201106/11/2011*********
Sheet1


And like this.....

Excel Workbook
ABCDEFGHIJKLM
1TaskOwnerStart dateEnd dateCompletion01 Nov 1102 Nov 1103 Nov 1104 Nov 1105 Nov 1106 Nov 1107 Nov 1108 Nov 11
2CleanBill03/11/201106/11/201106/11/2011********
Sheet1


Here's a copy of the file for you to look at....
Sing2Trees1983.xls


Good luck.

Ak
 
Upvote 0
Hi AK,

Many thanks for that - it is very much appreciated!

The formatting works great - but I would like to make two adjustments - if possible?
The way the date changes colour is spot on, however I might have 100 tasks for example, and would want the date to change colour on the corresponding line. I.e. if Task 10 (a11) is 1-2nd November, then F11 and F12 would change to orange?
The only way I can do it is by manually copying the conditional formatting rule into each row (and adjusting it) and then have the same date in each line. (e.g. 01 Nov 11 in F2, F3, F4 etc)

Secondly, is it possible to have a different colour depending on whose name is entered as the owner? I.e. Bill is orange, peter is blue etc

Thanks again in advance

:)
 
Upvote 0
Hi,

Is this what you mean?....


Excel Workbook
ABCDEFGHIJKLMNOPQR
1Owner ListTaskOwnerStart dateEnd dateCompletion01 Nov 1102 Nov 1103 Nov 1104 Nov 1105 Nov 1106 Nov 1107 Nov 1108 Nov 1109 Nov 1110 Nov 1111 Nov 1112 Nov 11
2BillCleanBill03/11/201106/11/201105/11/2011************
3PeterWashPeter02/11/201104/11/2011*************
4JackPaintJack01/11/201107/11/2011*************
5*WashBill07/11/201109/11/2011*************
6*PaintPeter05/11/201109/11/2011*************
7*CleanJack08/11/201111/11/2011*************
Sheet1


The first formula needs changing to this for Bill....
=AND($C2=$A$2,$F2="",G$1>=$D2,G$1<=$E2)
This for Peter...
=AND($C2=$A$3,$F2="",G$1>=$D2,G$1<=$E2)
This for Jack...
=AND($C2=$A$4,$F2="",G$1>=$D2,G$1<=$E2)

You will have to add a list of names so that you can refer to them in your formulas. You must have Excel 2007 or 2010 for this to work.

If you require the relevant dates to be displayed in the highlighted cells....
Highlight your range from G2:AJ7
In G2 put this formula...
=IF(AND(G$1>=$D2,G$1<=$E2),G$1,"")
Press ctrl and Enter

Excel Workbook
ABCDEFGHIJKLMNOPQR
1Owner ListTaskOwnerStart dateEnd dateCompletion01 Nov 1102 Nov 1103 Nov 1104 Nov 1105 Nov 1106 Nov 1107 Nov 1108 Nov 1109 Nov 1110 Nov 1111 Nov 1112 Nov 11
2BillCleanBill03/11/201106/11/2011***03 Nov 1104 Nov 1105 Nov 1106 Nov 11******
3PeterWashPeter02/11/201104/11/2011**02 Nov 1103 Nov 1104 Nov 11********
4JackPaintJack01/11/201107/11/201106/11/201101 Nov 1102 Nov 1103 Nov 1104 Nov 1105 Nov 1106 Nov 1107 Nov 11*****
5*WashBill07/11/201109/11/2011*******07 Nov 1108 Nov 1109 Nov 11***
6*PaintPeter05/11/201109/11/2011*****05 Nov 1106 Nov 1107 Nov 1108 Nov 1109 Nov 11***
7*CleanJack08/11/201111/11/2011********08 Nov 1109 Nov 1110 Nov 1111 Nov 11*
Sheet1



For the updated file....
Sing2Trees1983.xls

These may not be the best solutions, but they do appear to work.

Good luck.

Ak
 
Upvote 0

Forum statistics

Threads
1,226,390
Messages
6,190,739
Members
453,615
Latest member
robbieb29

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top