Forum Discussion

elizabethiw's avatar
elizabethiw
Copper Contributor
Jul 09, 2024
Solved

How do I colour a cell based on text and a date range in a Gantt chart?

Hi there, I'm looking for help working out how to do the below. 
Here is the set up: 

Column A - Event title 

Column B - Organiser 

Column C - Start date 

Column D - End date 

From Column F is a Gantt chart with dates at the top.

 

I am currently using conditional formatting to auto colour the cells on the Gantt chart under the start date through to the end date for each event.  The formula I am using for this is: 
=AND(F$6>=$D8,F$6<=$E8)

I want to now distinguish between organisers using different colours on the cells from the start date  through to the end date e.g.  column B lists the organiser as Elizabeth and column C has a start date of 23/7/24 and Column D has an end date of 26/7/24 so the coloured cells on the Gantt chart will be yellow.  Where as if the organiser was Dave then the cells would be green between those two dates. 

I'm thinking I need to use an IF formula in addition to my current formula but I cannot work it out - would anyone know how to do this?

Many thanks in advance, 

Resources