Change cells' color with VBA based off a pair of cells

[lyonder2000]

I'm working on a task to create a project plan (if you will) in Excel. I will have a Start Date and End Date in adjacent cells. These cells are set up as lists. To the right are corresponding date cells like a timeline.

When a start date and end date are selected in the respective columns, I need to have the color start on the cell for that range in the timeline and that color should carry out to the end date cell in the timeline.

I've tried conditional formatting, but my need to have different colors on different tasks is restricting me from using conditional formatting.

Any thoughts or base VBA code to get started would be greatly appreciated.
Thanks.
(Excel 2007)

 

[Scott Huish]

Excel 2007 isn't limited to 3 formats, why can't you use Conditional Formatting?

 

[lyonder2000]

Can you please provide an example or instruction? Maybe I'm just lacking the knowledge to build the necessary formulas with conditional formatting.

I was able to do something similar with conditional formatting as long as my start date was always the same, but with varying start dates, I could not make it work.

 

[Scott Huish]

Can you provide a sample of your data, and the results you expect.

Excel Workbook
	A
1
Sheet1

 

[lyonder2000]

See below. My Start and End Dates are drop down lists of all the timeline dates listed in F1:Q1. The colored cells should change according to the Start Date and End Date selected.

Thanks again for assisting.


Main

<table style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="WIDTH: 30px; FONT-WEIGHT: bold"> <col style="WIDTH: 298px"> <col style="WIDTH: 103px"> <col style="WIDTH: 108px"> <col style="WIDTH: 92px"> <col style="WIDTH: 19px"> <col style="WIDTH: 73px"> <col style="WIDTH: 73px"> <col style="WIDTH: 73px"> <col style="WIDTH: 73px"> <col style="WIDTH: 73px"> <col style="WIDTH: 73px"> <col style="WIDTH: 73px"> <col style="WIDTH: 73px"> <col style="WIDTH: 73px"> <col style="WIDTH: 73px"> <col style="WIDTH: 73px"> <col style="WIDTH: 73px"></colgroup> <tbody> <tr style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"> <td>
</td> <td>A</td> <td>B</td> <td>C</td> <td>D</td> <td>E</td> <td>F</td> <td>G</td> <td>H</td> <td>I</td> <td>J</td> <td>K</td> <td>L</td> <td>M</td> <td>N</td> <td>O</td> <td>P</td> <td>Q</td></tr> <tr style="HEIGHT: 57px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 12pt; FONT-WEIGHT: bold">Project Name</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 12pt; FONT-WEIGHT: bold">Owner</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 12pt">Start Date</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 12pt">End Date</td> <td>
</td> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffcc99; FONT-WEIGHT: bold">Q1
2010</td> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffcc99; FONT-WEIGHT: bold">Q2
2010</td> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffcc99; FONT-WEIGHT: bold">Q3
2010</td> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffcc99; FONT-WEIGHT: bold">Q4
2010</td> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ff9900; FONT-WEIGHT: bold">Q1
2011</td> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ff9900; FONT-WEIGHT: bold">Q2
2011</td> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ff9900; FONT-WEIGHT: bold">Q3
2011</td> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ff9900; FONT-WEIGHT: bold">Q4
2011</td> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffcc99; FONT-WEIGHT: bold">Q1
2012</td> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffcc99; FONT-WEIGHT: bold">Q2
2012</td> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffcc99; FONT-WEIGHT: bold">Q3
2012</td> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffcc99; FONT-WEIGHT: bold">Q4
2012</td></tr> <tr style="HEIGHT: 19px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</td> <td style="FONT-WEIGHT: bold">SDC Program </td> <td style="BACKGROUND-COLOR: #ff0000; FONT-WEIGHT: bold">Xu</td> <td>Q1 / 2010</td> <td>Q3 / 2010</td> <td>
</td> <td style="BACKGROUND-COLOR: #99ccff">
</td> <td style="BACKGROUND-COLOR: #99ccff">
</td> <td style="BACKGROUND-COLOR: #99ccff">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td></tr> <tr style="HEIGHT: 19px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</td> <td style="FONT-WEIGHT: bold">Analytics</td> <td style="BACKGROUND-COLOR: #ffff00; FONT-WEIGHT: bold">Pope</td> <td>Q2 / 2010</td> <td>Q4 / 2010</td> <td style="BACKGROUND-COLOR: #ffffcc">
</td> <td>
</td> <td style="BACKGROUND-COLOR: #ffcc99">
</td> <td style="BACKGROUND-COLOR: #ffcc99">
</td> <td style="BACKGROUND-COLOR: #ffcc99">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td></tr> <tr style="HEIGHT: 19px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</td> <td style="FONT-WEIGHT: bold">Adjustment Analysis</td> <td style="BACKGROUND-COLOR: #ffff00; FONT-WEIGHT: bold">Pope</td> <td>Q4 / 2010</td> <td>Q4 / 2011</td> <td style="BACKGROUND-COLOR: #ffffcc">
</td> <td>
</td> <td>
</td> <td>
</td> <td style="BACKGROUND-COLOR: #ccccff">
</td> <td style="BACKGROUND-COLOR: #ccccff">
</td> <td style="BACKGROUND-COLOR: #ccccff">
</td> <td style="BACKGROUND-COLOR: #ccccff">
</td> <td style="BACKGROUND-COLOR: #ccccff">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td></tr></tbody></table>

Excel tables to the web >> Excel Jeanie HTML 4

 

[lyonder2000]

Was this example able to help with the direction I'm attempting to take?