SOLVED

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

Copper 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?

2 Replies
best response confirmed by HansVogelaar (MVP)
Solution

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

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

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

@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

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

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