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​

<TBODY>
[TD="width: 16%"]

FINISH​

[/TD]
[TD="width: 8%"][/TD]
[TD="width: 7%"]

07-Jul-14​

[/TD]
[TD="width: 7%"]

14-Jul-14​

[/TD]
[TD="width: 7%"]

21-Jul-14​

[/TD]
[TD="width: 7%"]

28-Jul-14​

[/TD]
[TD="width: 7%"]

04-Aug-14​

[/TD]
[TD="width: 7%"]

11-Aug-14​

[/TD]

[TD="width: 16%"]

A​

[/TD]
[TD="width: 16%"]

29-May-14​

[/TD]
[TD="width: 16%"]

07-Aug-14​

[/TD]
[TD="width: 8%"][/TD]
[TD="width: 7%"][/TD]
[TD="width: 7%"][/TD]
[TD="width: 7%"][/TD]
[TD="width: 7%"][/TD]
[TD="width: 7%"][/TD]
[TD="width: 7%"][/TD]

[TD="width: 16%"]

B​

[/TD]
[TD="width: 16%"]

05-Jun-14​

[/TD]
[TD="width: 16%"]

22-Aug-14​

[/TD]
[TD="width: 8%"][/TD]
[TD="width: 7%"][/TD]
[TD="width: 7%"][/TD]
[TD="width: 7%"][/TD]
[TD="width: 7%"][/TD]
[TD="width: 7%"][/TD]
[TD="width: 7%"][/TD]

[TD="width: 16%"]

C​

[/TD]
[TD="width: 16%"]

30-Jun-14​

[/TD]
[TD="width: 16%"]

23-Sep-14​

[/TD]
[TD="width: 8%"][/TD]
[TD="width: 7%"][/TD]
[TD="width: 7%"][/TD]
[TD="width: 7%"][/TD]
[TD="width: 7%"][/TD]
[TD="width: 7%"][/TD]
[TD="width: 7%"][/TD]

[TD="width: 16%"]

D​

[/TD]
[TD="width: 16%"]

22-Jul-14​

[/TD]
[TD="width: 16%"]

04-Aug-14​

[/TD]
[TD="width: 8%"][/TD]
[TD="width: 7%"][/TD]
[TD="width: 7%"][/TD]
[TD="width: 7%"][/TD]
[TD="width: 7%"][/TD]
[TD="width: 7%"][/TD]
[TD="width: 7%"][/TD]

[TD="width: 16%"]

E​

[/TD]
[TD="width: 16%"]

08-Sep-14​

[/TD]
[TD="width: 16%"]

24-Oct-14​

[/TD]
[TD="width: 8%"][/TD]
[TD="width: 7%"][/TD]
[TD="width: 7%"][/TD]
[TD="width: 7%"][/TD]
[TD="width: 7%"][/TD]
[TD="width: 7%"][/TD]
[TD="width: 7%"][/TD]

</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]

[TABLE="width: 572"]
<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>[TR]
[TD="class: xl120, width: 72, bgcolor: transparent"]ACTIVITY[/TD]
[TD="class: xl120, width: 75, bgcolor: transparent"]START[/TD]
[TD="class: xl120, width: 75, bgcolor: transparent"]FINISH[/TD]
[TD="class: xl120, width: 75, bgcolor: transparent"] [/TD]
[TD="class: xl123, width: 50, bgcolor: transparent, align: right"]07-Jul-14[/TD]
[TD="class: xl123, width: 34, bgcolor: transparent, align: right"]14-Jul-14[/TD]
[TD="class: xl123, width: 34, bgcolor: transparent, align: right"]21-Jul-14[/TD]
[TD="class: xl123, width: 34, bgcolor: transparent, align: right"]28-Jul-14[/TD]
[TD="class: xl123, width: 34, bgcolor: transparent, align: right"]04-Aug-14[/TD]
[TD="class: xl123, width: 34, bgcolor: transparent, align: right"]11-Aug-14[/TD]
[TD="class: xl123, width: 34, bgcolor: transparent, align: right"]18-Aug-14[/TD]
[TD="class: xl123, width: 34, bgcolor: transparent, align: right"]25-Aug-14[/TD]
[TD="class: xl123, width: 34, bgcolor: transparent, align: right"]01-Sep-14[/TD]
[TD="class: xl123, width: 34, bgcolor: transparent, align: right"]08-Sep-14[/TD]
[TD="class: xl123, width: 34, bgcolor: transparent, align: right"]15-Sep-14[/TD]
[TD="class: xl123, width: 34, bgcolor: transparent, align: right"]22-Sep-14[/TD]
[TD="class: xl123, width: 34, bgcolor: transparent, align: right"]29-Sep-14[/TD]
[/TR]
[TR]
[TD="class: xl122, width: 72, bgcolor: transparent"]A[/TD]
[TD="class: xl121, bgcolor: transparent"]28-Jul-14[/TD]
[TD="class: xl121, bgcolor: transparent"]25-Aug-14[/TD]
[TD="class: xl127, bgcolor: transparent"][/TD]
[TD="class: xl124, bgcolor: transparent"][/TD]
[TD="class: xl125, bgcolor: transparent"] [/TD]
[TD="class: xl125, bgcolor: transparent"] [/TD]
[TD="class: xl125, bgcolor: transparent"] [/TD]
[TD="class: xl125, bgcolor: transparent"] [/TD]
[TD="class: xl125, bgcolor: transparent"] [/TD]
[TD="class: xl125, bgcolor: transparent"] [/TD]
[TD="class: xl125, bgcolor: transparent"] [/TD]
[TD="class: xl125, bgcolor: transparent"] [/TD]
[TD="class: xl125, bgcolor: transparent"] [/TD]
[TD="class: xl125, bgcolor: transparent"] [/TD]
[TD="class: xl125, bgcolor: transparent"] [/TD]
[TD="class: xl125, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl122, width: 72, bgcolor: transparent"]B[/TD]
[TD="class: xl121, bgcolor: transparent"]07-Jul-14[/TD]
[TD="class: xl121, bgcolor: transparent"]25-Aug-14[/TD]
[TD="class: xl121, bgcolor: transparent"] [/TD]
[TD="class: xl125, bgcolor: transparent"] [/TD]
[TD="class: xl125, bgcolor: transparent"] [/TD]
[TD="class: xl125, bgcolor: transparent"] [/TD]
[TD="class: xl125, bgcolor: transparent"] [/TD]
[TD="class: xl125, bgcolor: transparent"] [/TD]
[TD="class: xl125, bgcolor: transparent"] [/TD]
[TD="class: xl125, bgcolor: transparent"] [/TD]
[TD="class: xl125, bgcolor: transparent"] [/TD]
[TD="class: xl125, bgcolor: transparent"] [/TD]
[TD="class: xl125, bgcolor: transparent"] [/TD]
[TD="class: xl125, bgcolor: transparent"] [/TD]
[TD="class: xl125, bgcolor: transparent"] [/TD]
[TD="class: xl125, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl122, width: 72, bgcolor: transparent"]C[/TD]
[TD="class: xl121, bgcolor: transparent"]25-Aug-14[/TD]
[TD="class: xl121, bgcolor: transparent"]22-Sep-14[/TD]
[TD="class: xl121, bgcolor: transparent"] [/TD]
[TD="class: xl125, bgcolor: transparent"] [/TD]
[TD="class: xl125, bgcolor: transparent"] [/TD]
[TD="class: xl125, bgcolor: transparent"] [/TD]
[TD="class: xl125, bgcolor: transparent"] [/TD]
[TD="class: xl125, bgcolor: transparent"] [/TD]
[TD="class: xl125, bgcolor: transparent"] [/TD]
[TD="class: xl125, bgcolor: transparent"] [/TD]
[TD="class: xl125, bgcolor: transparent"] [/TD]
[TD="class: xl125, bgcolor: transparent"] [/TD]
[TD="class: xl125, bgcolor: transparent"] [/TD]
[TD="class: xl125, bgcolor: transparent"] [/TD]
[TD="class: xl125, bgcolor: transparent"] [/TD]
[TD="class: xl125, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl122, width: 72, bgcolor: transparent"]D[/TD]
[TD="class: xl121, bgcolor: transparent"]21-Jul-14[/TD]
[TD="class: xl121, bgcolor: transparent"]04-Aug-14[/TD]
[TD="class: xl121, bgcolor: transparent"] [/TD]
[TD="class: xl125, bgcolor: transparent"] [/TD]
[TD="class: xl125, bgcolor: transparent"] [/TD]
[TD="class: xl125, bgcolor: transparent"] [/TD]
[TD="class: xl125, bgcolor: transparent"] [/TD]
[TD="class: xl125, bgcolor: transparent"] [/TD]
[TD="class: xl125, bgcolor: transparent"] [/TD]
[TD="class: xl125, bgcolor: transparent"] [/TD]
[TD="class: xl125, bgcolor: transparent"] [/TD]
[TD="class: xl125, bgcolor: transparent"] [/TD]
[TD="class: xl125, bgcolor: transparent"] [/TD]
[TD="class: xl125, bgcolor: transparent"] [/TD]
[TD="class: xl125, bgcolor: transparent"] [/TD]
[TD="class: xl125, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl122, width: 72, bgcolor: transparent"]E[/TD]
[TD="class: xl121, bgcolor: transparent"]08-Sep-14[/TD]
[TD="class: xl121, bgcolor: transparent"]29-Oct-14[/TD]
[TD="class: xl121, bgcolor: transparent"] [/TD]
[TD="class: xl125, bgcolor: transparent"] [/TD]
[TD="class: xl125, bgcolor: transparent"] [/TD]
[TD="class: xl125, bgcolor: transparent"] [/TD]
[TD="class: xl125, bgcolor: transparent"] [/TD]
[TD="class: xl125, bgcolor: transparent"] [/TD]
[TD="class: xl125, bgcolor: transparent"] [/TD]
[TD="class: xl125, bgcolor: transparent"] [/TD]
[TD="class: xl125, bgcolor: transparent"] [/TD]
[TD="class: xl125, bgcolor: transparent"] [/TD]
[TD="class: xl125, bgcolor: transparent"] [/TD]
[TD="class: xl125, bgcolor: transparent"] [/TD]
[TD="class: xl125, bgcolor: transparent"] [/TD]
[TD="class: xl125, bgcolor: transparent"] [/TD]
[/TR]
</TBODY>[/TABLE]