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 exactly is the criteria? Which date change & which blank cells?

 

[MRKadar]

the goal is to achieve a timeline, with activity dates and bar charts. I would like to change the date range which as a consequence colours the bar chart automatically to reflect the date change.

 

[MRKadar]

making reference to the table above. i would like to change the START and FINISH date and as result i would like the cells that correspond to that data range to fill with a colour i.e. 7jul2014 to 10sept2015.

 

[gaz_chops]

So in Activity "A", you want the cells between column 29-may-14 & 07-Aug-14 to be highlighted!

 

[MRKadar]

Yes. and also when I change the date range, i would like the highlighted cells to change accordingly.

 

[gaz_chops]

Try this in conditional formatting

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

Assuming
B = Start Date
C = Finish Date
E$1 = 1st column with date in

 

[MRKadar]

Hi Gaz, it doesnt really work. It highlights ramdom cells which dont make any sence. Can you help please?

 

[gaz_chops]

What cells are your data in? If you can post a sample of spreadsheet it would be easier.

 

[MRKadar]

ACTIVITY

START

FINISH

07-Jul-14

14-Jul-14

21-Jul-14

28-Jul-14

04-Aug-14

11-Aug-14

18-Aug-14

25-Aug-14

01-Sep-14

08-Sep-14

15-Sep-14

22-Sep-14

29-Sep-14

A

28-Jul-14

25-Aug-14

B

07-Jul-14

25-Aug-14

C

25-Aug-14

22-Sep-14

D

21-Jul-14

04-Aug-14

E

08-Sep-14

29-Oct-14

<COLGROUP><COL style="WIDTH: 54pt" width=72><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2400" span=2 width=75><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2400" width=75><COL style="WIDTH: 38pt; mso-width-source: userset; mso-width-alt: 1600" width=50><COL style="WIDTH: 26pt; mso-width-source: userset; mso-width-alt: 1088" span=12 width=34><TBODY>
</TBODY>