+ Reply to Thread
Results 1 to 23 of 23

Color cells based on start and end dates and continue coloring based cycle days On & Off

  1. #1
    Registered User
    Join Date
    07-06-2015
    Location
    Central, Oregon
    MS-Off Ver
    2013
    Posts
    56

    Color cells based on start and end dates and continue coloring based cycle days On & Off

    Hello All,

    I am hoping one of you gurus have a solution in my case. I have need to create a rotation schedule. I am having difficulty trying to figure out how to do this with my limited knowledge. I have a list with Names for a crew of up to 200 guys in rows in column A, a Start date in column B, an End date in column C, a schedule ON shift in days in column D, and a schedule OFF time in days in Column E then a group of cells in columns to the right beginning from a sheets rotation schedule reference date that is manually updated for a given day in the year. So the values in the cells could be a hidden date reference or whatever makes sense for conditional formatting or VBA if that's the best approach. All of the start and end dates would be near term and should be after the rotation schedule reference date by design so they would show up on the schedule. I need enough cells to the right to populate for a years worth of days from the rotation schedule reference date. I want to be able to take all of the cells between the start and end date and shade them in each row then when the last cell or end date cell is complete I want to use the column E schedule OFF time and skip that many days and then begin shading the next cell for the number of ON days from column D and then keep going until those days are complete and then begin the schedule OFF again until done then repeat ON and OFF then ON and OFF until complete to the end of the years worth of cells. Does this make sense? I am taking a persons first schedule which might not be an exact match of ON days and then projecting what it would be once ended based on a number of days cycle entered. Probably a drop list maybe. For example 28 days ON and 14 days OFF is most typical in my environment. It doesn't appear to be too complicated except until I try to wrap my head around how to do it but I have trouble with simple things. I was not sure if could be done without VBA which I'm not savy with at all. I can use a lot of formulas but nesting all the functional requirements to satisfy this has me unable to break it down in conditional formatting. Although maybe it will need to be VBA so in that case a button maybe to refresh and go from top to bottom in the name list until there's no more makes more sense. I would greatly appreciate your help.

    I have included an attachment that is more or less like what I described above but columns are different because of what I actually have from a short term Gantt chart now. I will have an On-Return date but it's not consistently available so I didn't plan to use it. I will need to start the OFF pattern first then the ON pattern as a rule. If I had something for the above description I think I can figure out how to change the references to match my attachment or whatever makes sense for the contributor or guru.

    Thank you in advance
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    08-16-2015
    Location
    Antwerpen, Belgium
    MS-Off Ver
    2007-2016
    Posts
    2,380

    Re: Color cells based on start and end dates and continue coloring based cycle days On & O

    This code after Button

    Please Login or Register  to view this content.
    Kind regards
    Leo
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    07-06-2015
    Location
    Central, Oregon
    MS-Off Ver
    2013
    Posts
    56

    Re: Color cells based on start and end dates and continue coloring based cycle days On & O

    Sir Leo,

    I am very impressed. I tried it and it's very close to what I need. The only thing is I probably didn't articulate well is. The given Start and End-Last are dates that are entered from a form the employee fills out. He'll return to begin work on the Start date and he will go out several weeks later on the End-Last date. It might be 28 days or more or less. So from the end of that End-Last day I will need to then start an OFF cycle coloring based on the SHIFT OFF DAYS and then when finished begin and ON cycle coloring based on the SHIFT ON DAYS until finished and then repeat until the end of the year in cell C2 given. Hopefully I articulated it well enough this time.

    Also I am still trying to decipher the changes you made to the months durations. I understand the name changes and I'd do the same to get in my native language. Maybe there is no impact on the code but I messed up the dates and the weeks and the months and I am sure that was of no help to you. I tried to fix it so they roll in the attached version of your spreadsheet. The month will be important to make use of this Gantt otherwise it will be difficult to understand where your at without knowing exactly which week is in what month and will be cumbersome. I'm not sure how to roll that because the merge would have to float to roll as well so I chose for now a different way to display it by dropping the merge and just added a month name for every column with the rolling function.

    So I just noticed somehow the Macro doesn't work anymore. Sorry to also need help troubleshooting that but I guess it would need a revision and test with my hopefully better definitions. I am still trying to figure out the code and where range M is defined but I'll try to decipher it to learn from this. Thank you Leo. Hopefully the attachment comes through. Forum appears to have some server problems from some work they are doing.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    07-06-2015
    Location
    Central, Oregon
    MS-Off Ver
    2013
    Posts
    56

    Re: Color cells based on start and end dates and continue coloring based cycle days On & O

    duplicated posting when having server response issues I may have been impatient...
    Last edited by KG869; 02-02-2017 at 12:37 PM. Reason: duplicated when having server response issues

  5. #5
    Forum Expert
    Join Date
    08-16-2015
    Location
    Antwerpen, Belgium
    MS-Off Ver
    2007-2016
    Posts
    2,380

    Re: Color cells based on start and end dates and continue coloring based cycle days On & O

    So whe have a cyclus 14 days off, 28 days on

    Start 02/07/2017
    End-Last 03/07/2017

    What date to start cyclus ?
    What date to end cyclus ?

    Kind regards
    Leo

  6. #6
    Registered User
    Join Date
    07-06-2015
    Location
    Central, Oregon
    MS-Off Ver
    2013
    Posts
    56

    Re: Color cells based on start and end dates and continue coloring based cycle days On & O

    Sir,

    I think I see what your asking. You have a the Start and End-Last dates in your reply. So for your question for each row starting at row 17 when the last work day (End-Last) is reached at 3/07/17 there will then begin 14 days for OFF cycle from that row's cell in column M called SHIFT OFF DAYS. Then after that OFF cycles' last day the ON cycle will start and go for the 28 days referenced in that row's cell for column L called SHIFT ON DAYS until ended then repeat OFF cycle then ON cycle and so on until the end of row at cell NN marked day 365 in row 6. So a rolling year. You could use row 16 as marker for all rows or even row 11 since they will remain in place to guide the days that roll.

    Does this make sense? Thank you so much Leo. You are most kind.

    Keith

  7. #7
    Forum Expert
    Join Date
    08-16-2015
    Location
    Antwerpen, Belgium
    MS-Off Ver
    2007-2016
    Posts
    2,380

    Re: Color cells based on start and end dates and continue coloring based cycle days On & O

    Maybe,

    Please Login or Register  to view this content.
    Cheers
    Leo
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    07-06-2015
    Location
    Central, Oregon
    MS-Off Ver
    2013
    Posts
    56

    Thumbs up Re: Color cells based on start and end dates and continue coloring based cycle days On & O

    Leo,

    The code you write is amazing. I'm trying to learn some of the referencing and routines but alas it'll take a while. So your code is getting closer and you nailed the cycle time very well and great job! What I still need is the beginning (Start) through the end (End-Last) date colored in Green also since they are working days ahead of the cycles. I need to show them on the Gantt chart. I manually colored them in gray in the attached so you could see what didn't get colored.

    Also the light red color at the beginning is supposed to be Green working ON day but for some reason it is ending up as a Red day or OFF day. It's only the first one in each row though so maybe a -1 needed? I tried a different duration and used 21 ON and 21 OFF cycle and it's is consistent there also with the first cell being Red instead of Green.

    The other thing I don't understand from the code is where it decides to end? It looks like it's coloring a total of 12 On/Off color change cycles. I can't see it in the code. Could it go to the last day of the year at column NN and stop without too much trouble? That will be a 365 day rolling calendar then. I am thinking it's easier written then coded but it seems your a man about taking on challenges.

    When you get all the color green in there I'll search and replace and put a 1 value in those boxes and do a subtotal at the bottom so I can get a man count for every day which will help us with staffing projections. Maybe if you aren't warn out by then we can put a 1 in the code at the same time it colors green and it won't have to be manual but either way I don't mind. This is cool stuff and I appreciate your talented help very much. I wish I could hang out in Belgium and buy you a few rounds after this one.

    Thank you very much Sir,

    Keith
    Attached Files Attached Files

  9. #9
    Forum Expert
    Join Date
    08-16-2015
    Location
    Antwerpen, Belgium
    MS-Off Ver
    2007-2016
    Posts
    2,380

    Re: Color cells based on start and end dates and continue coloring based cycle days On & O

    Hi Keith,

    not with values 1 in green fields, but function to count the green rows for each column


    Please Login or Register  to view this content.

    Kind regards
    Leo
    Attached Files Attached Files
    Last edited by LeoTaxi; 02-04-2017 at 08:01 PM.

  10. #10
    Registered User
    Join Date
    07-06-2015
    Location
    Central, Oregon
    MS-Off Ver
    2013
    Posts
    56

    Re: Color cells based on start and end dates and continue coloring based cycle days On & O

    King Leo,

    Absolutely outstanding!! I am in a debt of gratitude. This is awesome. I am awestruck at the code and your generosity. Please let me know how I can repay you? You get as much reputation as I can get out of the Add Reputation button. Thank you sir. Please connect with me if you ever get in the area of the North West in the USA.

    I'm not sure if I'll need any more on this as it looks so good but I'll send you a quick note if something needs a slight tweek. Hopefully I can figure it out but alas it might be a while. Thank you Sir!

    Keith

  11. #11
    Forum Expert
    Join Date
    08-16-2015
    Location
    Antwerpen, Belgium
    MS-Off Ver
    2007-2016
    Posts
    2,380

    Re: Color cells based on start and end dates and continue coloring based cycle days On & O

    You're welcome

    Cheers
    Leo

  12. #12
    Registered User
    Join Date
    07-06-2015
    Location
    Central, Oregon
    MS-Off Ver
    2013
    Posts
    56

    Re: Color cells based on start and end dates and continue coloring based cycle days On & O

    Leo,

    As with everything it helps to have all the parameters. So I apologize for not giving you this information. As I learn to read the VB code more I may be able to anticipate this type of problem. Hopefully the work around is not too time consuming.

    I am using data for the columns for names and dates that comes from another work book. The work book is a source Gantt chart that has a long history and multiple worksheets feeding off it. I'm not in control of the format so it's easier to change this new schedule forecasting tool you've put your efforts into. The problem I've found is that the format of the source has some blank data for when the categories they are separated by have names that get added and then resorted by groups. This leaves spaces in the source data between rows which hangs up the code your wrote for me. If you could look at what I am uploading and see if there is a simple fix. I found in another test after remove blank rows that if for some reason the source sheets has missing Start and End-List dates it also hangs there. This is the case where we might have someone who is out for a reason such as a leave of absence but they are still in the source Gantt list. It would be best if it would skip these rows and blank dates ones. So I found the hang up point(s) I suppose possibly others exist and determined the cause but alas I don't know how to correct it without causing more problems to it. I am sorry to be of trouble.

    You will note a few changes but should be nothing that creates difficulties for you. My hope.

    Thank you sir!

    Keith
    Attached Images Attached Images
    Attached Files Attached Files
    Last edited by KG869; 02-05-2017 at 04:54 PM.

  13. #13
    Forum Expert
    Join Date
    08-16-2015
    Location
    Antwerpen, Belgium
    MS-Off Ver
    2007-2016
    Posts
    2,380

    Re: Color cells based on start and end dates and continue coloring based cycle days On & O

    This skips the blanco dates, also columns are color till NN

    Kind regards
    Leo
    Attached Files Attached Files

  14. #14
    Registered User
    Join Date
    07-06-2015
    Location
    Central, Oregon
    MS-Off Ver
    2013
    Posts
    56

    Re: Color cells based on start and end dates and continue coloring based cycle days On & O

    Leo,

    Thank you sir. I too am away but as soon as I get to opportunity to plug into my Excel I will use. Thank you kindly sir very much. I hope your travels were enjoyable.

  15. #15
    Registered User
    Join Date
    07-06-2015
    Location
    Central, Oregon
    MS-Off Ver
    2013
    Posts
    56

    Re: Color cells based on start and end dates and continue coloring based cycle days On & O

    Leo,

    Excellend, the changes work exactly as you wrote. I am wondering though why when cell C12 is advanced or altered that some of the green cells in the row dates become without green shading? I can't determine if it's not coloring them or somehow is removing the color in further section of the code. Sir, could you look at the code for the "Display Week" and referenced date in Cell C13 and see what is affecting that? You will notice the no fill cells in the columns N to NN when C12 is adjusted.

    Thank you kind sir,

    Keith

  16. #16
    Forum Expert
    Join Date
    08-16-2015
    Location
    Antwerpen, Belgium
    MS-Off Ver
    2007-2016
    Posts
    2,380

    Re: Color cells based on start and end dates and continue coloring based cycle days On & O

    made correction in this

    Kind regards
    Leo
    Attached Files Attached Files

  17. #17
    Registered User
    Join Date
    07-06-2015
    Location
    Central, Oregon
    MS-Off Ver
    2013
    Posts
    56

    Re: Color cells based on start and end dates and continue coloring based cycle days On & O

    Sir,

    You have corrected the on days to all green and that's working well from what I see in your spreadsheet. Kudos~! There's only one anomaly I see that is not occuring each row. Some of the OFF dates are now no fill instead of blue. No everyone of them however just some. I am uploading the file with those cells colored red to illustrate which ones I mean. I am thinking you are close to the solution but it's like catching a frog. Sometimes it's difficult.

    Warm regards,

    Keith
    Attached Files Attached Files

  18. #18
    Forum Expert
    Join Date
    08-16-2015
    Location
    Antwerpen, Belgium
    MS-Off Ver
    2007-2016
    Posts
    2,380

    Re: Color cells based on start and end dates and continue coloring based cycle days On & O

    Next try



    Kind regards
    Leo
    Attached Files Attached Files

  19. #19
    Registered User
    Join Date
    07-06-2015
    Location
    Central, Oregon
    MS-Off Ver
    2013
    Posts
    56

    Re: Color cells based on start and end dates and continue coloring based cycle days On & O

    Leo sir,

    It is closer. It works sometimes, but sometimes it errors and hangs on Case 1 when the Display Week advance is adjusted or even on repeats with Do Shading. Other times not. When it does it completes the color of the first row and stops. For some reason it colors NN +1 or NO / 379. Frogs are slippery. I am still very impressed on your skill but for myself I can't offer much more than observed symptoms to help. I included it back to you in the spot it is hung only for reference. Thank you, Keith

  20. #20
    Forum Expert
    Join Date
    08-16-2015
    Location
    Antwerpen, Belgium
    MS-Off Ver
    2007-2016
    Posts
    2,380

    Re: Color cells based on start and end dates and continue coloring based cycle days On & O

    Never give up

    next try


    Kind regards
    Leo

  21. #21
    Registered User
    Join Date
    07-06-2015
    Location
    Central, Oregon
    MS-Off Ver
    2013
    Posts
    56

    Re: Color cells based on start and end dates and continue coloring based cycle days On & O

    Leo the Man!! You certainly didn't and your #1 Mr.! That is very cool stuff. Thank you sir. My deep appreciation! I can't thank you enough. How can I possibly repay you. Please let me know.

  22. #22
    Forum Expert
    Join Date
    08-16-2015
    Location
    Antwerpen, Belgium
    MS-Off Ver
    2007-2016
    Posts
    2,380

    Re: Color cells based on start and end dates and continue coloring based cycle days On & O

    You can buy me 1 beer when we meet

    Cheers
    Leo

  23. #23
    Registered User
    Join Date
    07-06-2015
    Location
    Central, Oregon
    MS-Off Ver
    2013
    Posts
    56

    Re: Color cells based on start and end dates and continue coloring based cycle days On & O

    It'll be the best beer available. Cheers!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Color a timeline based on start and end values in two cells
    By Schmoranz in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-08-2014, 09:23 AM
  2. [SOLVED] Creating a formula to populate range of cells between dates based on start and end dates
    By Rainmain82 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-20-2014, 07:42 PM
  3. [SOLVED] Creating a formula to populate range of cells between dates based on start and end dates
    By Rainmain82 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-18-2014, 07:55 AM
  4. Color coding cells based on due dates for a series of milestones
    By popeye2295 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 08-05-2013, 04:24 PM
  5. [SOLVED] Coloring Cells based on adjacent cell color
    By smugglersblues in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 01-04-2013, 09:36 PM
  6. Highlighting range of cells based on start and end dates
    By d0gp1l3 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 07-13-2012, 12:36 PM
  7. Code to change color and value in calendar cells based on selected dates
    By John74 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-18-2010, 02:01 PM

Tags for this Thread

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