How to add colours to the timeline

Hi,
How could you identify which colour to choose?

Column F will have what type of activity it is
Rollout_Plan_02082021_V1.xlsx

once I change the date

Which one of the date columns do you want to compare with I15-BK15, Start (C$) or End (D$)?

start date please

I have added a button to the first sheet (EMEA T1) called "Set Colour" which calls a macro to colour the right pane according N8-13 & O8-13 columns. you might need to change the macro to suite your needs.
Rollout_Plan_02082021_V1 (1).xlsm

Just bear in mind the values you put in Column F should be exactly same as one of the values in  O8-13 to match its colour otherwise it won't be recognised. 

thanks Amir,

i will test and come back to you, thanks for the quick help

You're welcome - the values in For-loop are fixed so feel free to change them if you want to add more dates to the columns.

Can also be done with Conditional Formatting, will work on it later and post with formats.

Rob, I am not sure if this could be done by Conditional Formatting? as they need to pick the colour from the specified cells in the same sheet, also there are two conditions to check.

Hi Amir,

Thank you for this sheet but it only works once when i open the file and then if i change the dates it doesnt work, also it doesnt work on US T1 tab.

could you please let me know how i can fix that?

Hi,
There is a button called "Set Colour" you need to click after you change the dates.

If you are happy with the above, I can apply the same code on US T1 tab.

For Conditional Formatting (CF) I am assuming the colours have been decided on beforehand, hence the colour options at the top of the sheet.

Doesn't matter that it has to meet two conditions, can use AND function to check for multiple conditions.

File attached (Work in Progress) with 3 of the colours already formatted using CF

Rollout_Plan_02082021_V1 (1).xlsx

Each of the calendar cells has this formula:
=IF($F16="","",IF(AND(I$15>=$C16,I$15<=$D16),MATCH($F16,$O$8:$O$13,0),""))

That will return blank or number 1 to 6 for each of the Stages in the columns where the date in row 15 is within Start and End Date range (column F must equal options in O8 to O13 which is easy to achieve with Data Validation). The CF then uses the number 1 to 6 to determine which colour to format.

Now with all 6 colours formatted on UK sheet and a couple of other amendments.

1) Dates in row 15 were populated manually, now just populate first date and others are formula driven, previous day + 1

2) Sum of number of devices, using + symbol within a SUM function defeats the object of the SUM; excel will add up the individaul items with the + symbols and then pass the result to the SUM function whereas separating each with a comma sends the list of items to the SUM function to sum up. Advantage of that is if one of the items happens to be an error or text using + will create an error in the end result whereas SUM ignores text and errors.

Rollout_Plan_02082021_V1 (1).xlsx

Rob, I like when you changed the font colour for the calendar cells and set them with the Fill colour to hide the 1-6 number values. The colour sets in Conditional Formatting are still fixed though so if the user changes the colour from N$8-13 cells then the conditional formatting won't apply the new colour.
Your solution is a lot better than the one I provided. 

Will also highlight all relevant cells if the date range is more than one day.

thank you both for this and helping out, i still cant get US T1 working, is there anyhting i have to do?

You just need to copy/paste the calendar cells from the EMEA T1  onto US T1 sheet.

Sorry guys i dont know but i am using this platform after a long time and it used to be points but now its not asking for anything.

is that correct