Forum Discussion
elizabethiw
Jul 09, 2024Copper Contributor
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,
=AND(F$6>=$C8,F$6<=$D8,$B8="Dave")
This is the rule for Dave in the attached sample file. For every different colour you have to enter a seperate rule for conditional formatting.
- OliverScheurichGold Contributor
=AND(F$6>=$C8,F$6<=$D8,$B8="Dave")
This is the rule for Dave in the attached sample file. For every different colour you have to enter a seperate rule for conditional formatting.
- elizabethiwCopper Contributor
OliverScheurich It works! Thank you so much, I really appreciate you taking the time to answer my question.