Certain cells fill with colour based on the dates I enter (gantt chart style)

**matticusbond** · 04-08-2014, 05:51 PM

Hi guys -

I'm trying to create a gantt in excel and i'm trying to figure out if there is a way that, when I enter a start and end date in their respective columns, it fills the relevant cells in the sheet to correspond.

So, for example in the screenshot, if i were to enter in the first row start date as Jan-6 and end date as Feb-4, the cells from Jan-6 to Feb-4 would autofill with a colour.

i've attached a screenshot of the sheet.

Thanks!
Screen Shot 2014-04-08 at 14.43.57.png

**FDibbins** · 04-08-2014, 06:16 PM

Hi and welcome to the forum

This can be done with Conditional Formatting and (probably) an IF(and) statement. However, please do not upload a picture of your file...rather, upload a sample of your workbook, showing what data you are working with, a few samples of your expected outcome is (manually entered is ok) and how you arrived at that. (exclude sensitive info). Pictures are pretty much impossible to edit, and no-one wants to re-type your data for you

Also, not all members can upload picture files (Company firewalls and stuff)

**matticusbond** · 04-08-2014, 06:24 PM

Thanks, Ford!

I've tried playing with conditional formatting and If statements, but can't seem to achieve the desired effect.
sorry for the screenshot - I've attached, instead, a file, and put in some sample values and results.

Thanks.
Matt
2014_Roadmap(Copy).xlsx

**tuph** · 04-08-2014, 06:25 PM

Conditional formatting will do this for you. Select the cells from F4:G30 then click on Conditional Formatting, New Rule.
For Sprint 1, select the option to use a formula and enter this:

Please Login or Register  to view this content.

Click no Format and set the formatting you want to apply then closed the dialog box. You will need to repeat this for each of the other sprints, adjusting the cell references as needed.

If the conditional formatting does not work when you type in some valid dates, edit the conditional formatting rule and remove and double quote marks that Excel has automatcially added to your formula.

Good luck with it! If you strike trouble, attached a copy of your actual workbook so we can see what you have done.