Timeline colour cells depending on data inputted in a different cell

[MRKadar]

I would like to colour blank cells depending on the date change in a different cell. Please see table below *** an example.

ACTIVITY​	START​	FINISH​		07-Jul-14​	14-Jul-14​	21-Jul-14​	28-Jul-14​	04-Aug-14​	11-Aug-14​
A​	29-May-14​	07-Aug-14​
B​	05-Jun-14​	22-Aug-14​
C​	30-Jun-14​	23-Sep-14​
D​	22-Jul-14​	04-Aug-14​
E​	08-Sep-14​	24-Oct-14​

<TBODY>
</TBODY>

 

[gaz_chops]

What are the cell references?
Is Activity in cell A1?

 

[MRKadar]

Yes mate. A1 to Q6

 

[gaz_chops]

Ok, try this formula in cell E3, copy paste down and across .
You should see 1 under the relevant dates, 2 under irrelevant dates!

=IF(AND(E$1>=$B3,E$1<$C3),"1","2")

 

[MRKadar]

Amazing mate. thank you very much

 

[gaz_chops]

Did that work as expected? If it did then the conditional format should work with the formula i posted earlier!

 

[MRKadar]

How do i return a colour (lets say blue) instead of the 1.

 

[gaz_chops]

Delete the formulas from all of the cells, then

Select the 1st cell you want to format
Select "Format" on the top menu
Then select "Conditional Formatting"
Click + (bottom left)
Select the drop down (where it says "Style"
Select "Classic"
Select the next drop down (where it says :Formatonly....."
Select "Use a formula...."
Enter the formula
=AND(E$1>=$B3,E$1<$C3)
Select next drop down "Format with"
Select "Fill"
Then Under "Background" Select the colour you want
Select OK
And OK again
Lastly make sure that you select the "Applies to" range, this is all the cells you want to apply it to

 

[MRKadar]

I'm using excel 2010.
what do you mean by "Click + (bottom left)"

 

[gaz_chops]

In 2010 i think you have an option "New Rule"
(I'm using 2010 for Mac, slightly different!)

 

[MRKadar]

Yes. the formula seems to be highlighting random cells strangely.