+ Reply to Thread
Results 1 to 9 of 9

Creating a Gantt based on values in cells

  1. #1
    Registered User
    Join Date
    08-30-2007
    Location
    Orlando, Florida
    MS-Off Ver
    MS Excel 2007
    Posts
    50

    Creating a Gantt based on values in cells

    Based on a Beginning Date Value and an Ending Date Value - I would like to color a range of Cells - the month (dates) are in the header row.

    For example: Beginning Date = 01/01/09
    Ending Date = 06/01/09

    I want the range of cells in the same row colored orange under Jan '09 to June '09.

    Thanks!

  2. #2
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464
    Thanks![/QUOTE]

    Do you mean there are two header rows, one with the beginning date in say C1, and the end date in C2?

    It might be better if you could upload a sample workbook so that we can understand the requirement in context.

    Rgds

  3. #3
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,126
    Use conditional formatting.
    Attached Files Attached Files
    Cheers
    Andy
    www.andypope.info

  4. #4
    Registered User
    Join Date
    08-30-2007
    Location
    Orlando, Florida
    MS-Off Ver
    MS Excel 2007
    Posts
    50

    Thanks - Can't Use Conditional Formatting - 4 separate conditions

    I can't use conditional formatting because there is an additional determining factor as to color.

    As you can see in the attachment - Column B - there is curriculum

    So the cells are colored dependent on the curriculum - Math=Blue, Reading=Red, Social Studies=Pink and Science = Green

    The Beginning Date header is Row 1, Column G.
    The Endng Date Header is Row 1, Column H

    Thanks!

    Quote Originally Posted by Richard Buttrey
    Thanks!
    Do you mean there are two header rows, one with the beginning date in say C1, and the end date in C2?

    It might be better if you could upload a sample workbook so that we can understand the requirement in context.

    Rgds[/QUOTE]
    Attached Files Attached Files

  5. #5
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464
    I'm still confused. You seem to be suggesting there are two sets of conditions which define the colour you want. One depends on the 'Discipline' field, and the other is dependent on two dates.

    Can you explain exactly how these relate to one another? Does one take precedence over another, or is one dependent on the other? Perhaps you could give a few examples of the colours you want in various cells and how you make that decision.

    Rgds

  6. #6
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,126
    code to be placed in sheet object.

    Please Login or Register  to view this content.
    vjboaz, it really help those trying to help you if you state what you have tried and why it did not work.
    If you have a non standard layout that you mention such.

    Or even better, as Richard mentioned, post an example.

  7. #7
    Registered User
    Join Date
    08-30-2007
    Location
    Orlando, Florida
    MS-Off Ver
    MS Excel 2007
    Posts
    50

    Gantt Chart

    I apologize for being so unclear in my explanation. In the attached sample, I think it indicates what the situation is.

    Yes, the color is dependent on the discipline - the cell that gets the color is defined by the beginning and ending date.

    I hope that helps. I just can't get the code to look at the range of dates in Row 1 (from J to AM) and then color the cell below it in the active row the appropriate color.

    Thanks again for all your help.

    Veronica
    Attached Files Attached Files

  8. #8
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,126
    So did you try my code?

    You will need to adjust the colorindex values I used as they do not match yours exactly.

    Also my code does not clear any existing formatting, unless the dates changing have no start or end.

    If you do need existing colours on a row clearing first just copy the line

    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    08-30-2007
    Location
    Orlando, Florida
    MS-Off Ver
    MS Excel 2007
    Posts
    50

    Thank you... Thank you Thank you....

    You are the best!!!!!

    It works like magic.

    Veronica (alias vjboaz)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1