SOLVED

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

Copper Contributor

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, 

2 Replies
best response confirmed by HansVogelaar (MVP)
Solution

@elizabethiw 

=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.

@OliverScheurich It works!  Thank you so much,  I really appreciate you taking the time to answer my question.   

1 best response

Accepted Solutions
best response confirmed by HansVogelaar (MVP)
Solution

@elizabethiw 

=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.

View solution in original post