1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel VBA - modified gantt chart with custom conditional formatting

Discussion in 'Business Applications' started by Powernoob, Feb 22, 2012.

Thread Status:
Not open for further replies.
Advertisement
  1. Powernoob

    Powernoob Thread Starter

    Joined:
    Feb 22, 2012
    Messages:
    5
    It's deceptively simple what I'm trying to do, but after hours puzzling over Excel VBA forums and tutorials, I'm ready to throw in the towel. I'm using Excel:MAC 2011.

    Each row in my sheet represents a year in the life of one job. What I'd like to be able to do is enter an employee's name in column C, and start and end dates in columns F and G. Then I want the script to dynamically pick up the background color for that employee (column D) and copy it into the appropriate start-end range in the calendar in columns I-NJ (autofilled with 2012 dates).

    Background colors for each employee are currently set in a key, and I have a script that lifts the appropriate color to each row of data (in column D). This part of the script actually works. :)

    Code:
    Set oName = Range("C6:C500")
        For Each Cell In oName
            Select Case Cell.Value
                Case "Lynnette"
                    Cell.Offset(0, 1).Interior.Color = [NM7].Interior.Color
                Case "Cheryl"
                    Cell.Offset(0, 1).Interior.Color = [NM11].Interior.Color
                Case "John"
                    Cell.Offset(0, 1).Interior.Color = [NM9].Interior.Color
                Case "Michelle"
                    Cell.Offset(0, 1).Interior.Color = [NM12].Interior.Color
                Case "Richard"
                    Cell.Offset(0, 1).Interior.Color = [NM8].Interior.Color
                Case "Clara"
                    Cell.Offset(0, 1).Interior.Color = [NM10].Interior.Color
                Case Else
                    Cell.Offset(0, 1).Interior.ColorIndex = xlNone
            End Select
        Next Cell
    What's giving me trouble is the calendar. I can get it to work with just one row, but getting it to work dynamically in each populated row is the real trick.

    Code:
    Set oDate = Range("I6:NJ6")
            For Each Cell In oDate
                Select Case Cell.Value
                    Case Range("F6").Value To Range("G6").Value
                        Cell.Interior.Color = Range("D6").Interior.Color
                    Case Else
                        Cell.Interior.ColorIndex = xlNone
                End Select
            Next Cell
    
    The above works like a charm...for row 6 only. But how can I get this to iterate through each populated row and do the same thing? Below is one of many approaches I've tried (does nothing as far as I can tell).

    Code:
    Set oDate = Range("I6:NJ500")
        For i = 6 To oDate.Rows.Count
            For j = 9 To oDate.Columns.Count
                Select Case Cell.Value
                    Case Cells(i, 6).Value To Cells(i, 7).Value
                        oDate(i, j).Cell.Interior.Color = Cells(i, 4).Cell.Interior.Color
                    Case Else
                        oDate(i, j).Font.ColorIndex = xlNone
                End Select
            Next j
        Next i
    End Sub
    Any help much appreciated. Using dynamically-named ranges instead of the crude "I6:NJ500" would be great, but the main thing is just to get the basic script to work. Also, despite the single range above, my data rows will NOT be contiguous, so I should not use CurrentRegion unless the script creates multiple ranges. Thanks!
     
  2. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Hi, welcome to the forum,
    I'd like to help,I don't work on a MAC but I imagine vba is vba.
    Could you post a sheet with waht you have, it's easier than figuring out your code.
    I don't know if there are special options for savnig the file on a MAC, but I work with Excel 2010 and 2003 so no problem there, make sure the macro's come with it.

    It's past midnight so I won't see your repsonse until tomorrow early.
     
  3. Powernoob

    Powernoob Thread Starter

    Joined:
    Feb 22, 2012
    Messages:
    5
    Keebellah, thanks. I got a start manually scrubbing data to produce an anonymous version, but it'll take some time to finish. I'm not sure if Excel will even let me remove autofill entries and similar bits of personally identifying data. May have to recreate the sheet from scratch. I'll try to post a link in the morning.

    In the meantime, if you or anyone else wants to take a crack at the script itself, I'm all ears, and most appreciative. :)
     
  4. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Try copying a chart you have into a work file as bitmap and put your explanation there, I'll see if I can build it from there
     
  5. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    I've (re)read you post more accurately and realize it's not a graph but a set of cells you want colored based upon a stat and enddate.

    I helped someone with something similar and am sure tou can use it.
    I'll have to edit and then post it in a new file for general use.
    There were two sheets, one fopr a full year and for a 'dynamic' year.

    I was personally more interested in the dynamic year because the 'gantt or bar chart' would adpat automatically to thet current date - two months and current date + 3 months for prognosis, these parameters were editable so you could make it go for a whole year ahead starting two months earlier or whatever.

    I'll see if I can pick it up from my archive and have something workable for you, don't hold your breath but I'm sure something will come from this.

    I assume the date format is mm-dd-yy ?
     
  6. Powernoob

    Powernoob Thread Starter

    Joined:
    Feb 22, 2012
    Messages:
    5
    I'm uploading a screenshot, and hope that gives you a better idea (as I feared, I'm not able to scrub all of the personal metadata out of my Excel files).

    You're correct, I'm coloring cells to create a bar graph, not creating a chart. Columns I through NJ contain dates for each day of the year, in each row. Date format shouldn't matter, since the text is hidden in those cells anyway. As you can see in the screenshot, Row 6 is the only one that actually works as intended...the trick now is to get each subsequent row to do the same thing by replicating the code:

    Code:
    Set oDate = Range("I6:NJ6")
            For Each Cell In oDate
                Select Case Cell.Value
                    Case Range("F6").Value To Range("G6").Value
                        Cell.Interior.Color = Range("D6").Interior.Color
                    Case Else
                        Cell.Interior.ColorIndex = xlNone
                End Select
            Next Cell
    I'd love if this thing could dynamically incorporate any year or any number of rows, but my main problem is just getting the above code (or some other approach to the same idea) to apply properly to the entire sheet. To give you an idea, I imagine the final sheet will have 100-150 discontinuous rows (each section grouped by job as you see here).

    Assignment.jpg
     
  7. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Try the attached sheet.
    I just iused the macro code as it is so no special things.

    The macro is triggered using the shortcut key Short + Ctrl + K ( Ctrl + uppercase K)
    But is MAC does not recognize the keys the macro is named CalendarChk

    You won't see the results until you run the macro

    This calendar will update every time you run the macro.

    I could not find the whole year in one based upon 365 days so you'll have two calendsars, one for the current year and one for the next year and this is automatic so if you open the file in 2013 the calendar for 2014 is appended.

    Try this first. layou and coluring I didn't do it and for you onneeded fields are there, do not remove or else it won't work.

    This is just to show that something can be done this way and if it works on a MAc.

    My Excel version is 2010

    excuse type errros, :) I'm doing this quick since I'm off for a few hours
     

    Attached Files:

  8. Powernoob

    Powernoob Thread Starter

    Joined:
    Feb 22, 2012
    Messages:
    5
    Wow, that's pretty cool! The macro runs on CTRL-SHIFT-K on the mac, so yes it does work.

    Now unfortunately, the code's quite a bit more complex than, well, my VBA skills, and it'll take me awhile to see if I can get it to do exactly what I want (duration bars color coded by employee name). I'm not sure if I want to get into auto-generating the calendar, or just drill down to the part where it draws the bars.

    I'll report back. Thanks very much for the help, Keebellah. :)
     
  9. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    I know the vba code is complicated, I wrote this somehwre in the middel; of 2010 and in the meanwhile I've gainde more experience and have been abloe to simply my vba code.

    I just added the coloring posibilities.

    There is an admin sheet with a list I named employees, next to the name the colo

    The employee list will dymacically adapt if you add or remove nemaes and the pick list in the Events sheet.

    If you add a new employye and give the cell beside it a color then you must give this cell the name of the employee, and this range name will be used to put the color.

    I have ataached an editted part and als it show you how it distinguishes betweebn the thrre types of events, like I said this was for somebody else by I have not editted it more than make it usefull for this posting.

    editiing is always possible.

    Onec the calendar is there you only have to refresh it if you add data.

    Play around with it maybe it wil offer something

    The second and thirs sets of dates will put a blok on the same line for that period
     

    Attached Files:

  10. Powernoob

    Powernoob Thread Starter

    Joined:
    Feb 22, 2012
    Messages:
    5
    Ok, I'm going to play around with this and see what I can do. It might take me a few days as other projects have to come first, but I will let you know how it goes and (hoping I get it to work), post final results. Again, much appreciate the help!

    P.S. - didn't in any way mean to suggest that your code is unnecessarily "complex," just that it's a lot more sophisticated than my own coding abilities. :)
     
  11. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1042241

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice