+ Reply to Thread
Results 1 to 13 of 13

Changing the background of a range based on text in the range.

  1. #1
    Forum Contributor
    Join Date
    02-16-2008
    Location
    Mansfield, TX
    Posts
    324

    Changing the background of a range based on text in the range.

    Hi, I have been tasked with creating a scheduling system for my work. There are three groups of shifts that can be selected, Opens, Swings, and Closes. I have been asked to make the cells turn red if a user selects a sequence of shifts. We are trying to avoid having a person working three scenarios:

    Close>Swing>Open
    Close>Off>Open
    Close>Open



    If they select the shift that meet this scenario, then I need the cells to turn red. The complete list of shifts are on the "Data" tab and the the three scenarios have been inputted into the "Jan" tab in E13:G15.


    I am using the code below to change the background of the cells in my worksheet based on what is in the cell. How can I modify it to accomplish the changes below and meet the criteria above?


    Please Login or Register  to view this content.
    Here is a link to the file to make things easier.
    2009_Manager_Schedule_.html

    Thanks!
    Clayton Grove

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258
    Hello Clayton,

    I am not following your question. I downloaded your file and can't see how these scenarios fit in with your layout. Everything on the "Jan" sheet is daily. There are no names, store or employee, that these shifts go with. Can you provide a better and more detailed example of what is happening?

    Sincerely,
    Leith Ross

  3. #3
    Forum Contributor
    Join Date
    02-16-2008
    Location
    Mansfield, TX
    Posts
    324
    Sorry if I am not making myself clear. I need the cells with thos shifts to turn red if one of those scenarios happen. If any of the shifts in the list of closes is followed immediately to the right by any of the shifts in the lists of opens I would like the cells to turn red. The names have not been inputted yet but the week flow from left to right and each line is a different person so employee "a" would be in d13:k13 and employees "b"-"e" would be on the rows below. Each week the employees will be in the same order.


    I hope that clears up the confusion!

    Thanks
    Clayton

  4. #4
    Valued Forum Contributor
    Join Date
    09-19-2008
    Location
    It varies ...
    MS-Off Ver
    Office365 - 64bit
    Posts
    862
    Perhaps not the most elegant solution, but this should do it:
    Please Login or Register  to view this content.
    I've made some comments in the code that might help you refine this a bit. Hope that helps. MM.
    MatrixMan.
    --------------------------------------
    If this - or any - reply helps you, remember to say thanks by clicking on *Add Reputation.
    If your issue is now resolved, remember to mark as solved - click Thread Tools at top right of thread.

  5. #5
    Valued Forum Contributor
    Join Date
    09-19-2008
    Location
    It varies ...
    MS-Off Ver
    Office365 - 64bit
    Posts
    862
    One other thing - you may already know this, but don't copy this into each sheet ... use the workbook sheetselectionchange event and copy something like this there:
    Please Login or Register  to view this content.
    Where you've got the code below set up in a module like this (noting that you don't need to pass the sheet name into the parameter list because it will be the active sheet, so the default behaviour will be fine):
    Please Login or Register  to view this content.

  6. #6
    Valued Forum Contributor
    Join Date
    09-19-2008
    Location
    It varies ...
    MS-Off Ver
    Office365 - 64bit
    Posts
    862
    I've just read your reply to Leith ... he's asked the same question as I did in the comments of the code about how to identify the employee. Giving part of the picture at a time will either just create rework and/or result in a less-than-ideal solution being built. In this case, to extend the code I gave you to cover subsequent weeks - now knowing that each line is an employee - we need to know how to identify which rows are employee rows.

    Knowing this, we could dynamically pick up the identifier for the employee (name or ID), find other rows for that employee, create a linear array to hold all the data from all the rows for that employee in sequence and then process it either as I have or some other way that Leith might come up with.

    No-one likes to build something that is so fragile that the moment you insert a new column or row, the thing falls over, or rely on 'XYZ' always being in the 4th row and column C or other such static setups (like requiring employees to always be in the same order). If you give the information all up front, we can build it so that it's more robust and will give you a better end result. Without this info, you'd end up with something that will run for a while but will fail if you change anything.

  7. #7
    Forum Contributor
    Join Date
    02-16-2008
    Location
    Mansfield, TX
    Posts
    324
    I definetlu want to make this as clear as possible, I have such limited experience with VBA that I don't really have any idea what is needed.

    As for identifying the employees, would it be better to have one range with he employee names on the data sheet? Then have the cells on the rest of the tabs pull reference that range with a data validation list?

    You guys are the experts. Tell me what you need me to do to make this work and I will do it.

    Thanks!
    Clayton

  8. #8
    Valued Forum Contributor
    Join Date
    09-19-2008
    Location
    It varies ...
    MS-Off Ver
    Office365 - 64bit
    Posts
    862
    To be perfectly honest, it doesn't really matter whether you split the monthly sheet into weeks or put a month's schedule on one line per resource; the latter is simpler to cater for and (I think) easier to read but either can be managed. To be a proper solution, we'll need to review all data in the period - in this case, for the year - so either put a month's schedule on one line per sheet for each resource and pick up the same employee's data in each month's sheet (to check the sequence of shifts over month end) or do it weekly and double up the process. At some point, you have to set a cutoff for this, in this case, I assume we're only processing a single file (for 1 year), so it wouldn't look at the 31 Dec -> 01 Jan period.

    In terms of layout, it comes down to useability and personal preference; in my view, I think the layout is too "busy" and could be simplified, unless you have a specific reason for splitting out weeks on different rows ... but in the end, whether it's done on a weekly split or a monthly layout the question remains: how do we identify rows in the schedule sheets that relate to a specific resource?

  9. #9
    Forum Contributor
    Join Date
    02-16-2008
    Location
    Mansfield, TX
    Posts
    324
    Ok, I have changed the cells that the employee names go in to reference cells d4:d8 on the "data" tab. Unfortunately I am stuck with the layout, as that is what the boss likes. Will the code above work for this? What else should be done to make this work better?

    Thanks.
    Clayton

    http://www.4shared.com/file/67847049...Schedule_.html

  10. #10
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258
    Hello Clayton,

    Assume that I am a new hire and you have to teach me how to this scheduler works. Walk me through the steps.

    Sincerely,
    Leith Ross

  11. #11
    Forum Contributor
    Join Date
    02-16-2008
    Location
    Mansfield, TX
    Posts
    324
    I would be glad to.


    1. On the Data sheet, enter the managers names in the box next to their position.
    2. On the Data sheet, Enter the desired shifts that will be used in the schedule.
    3. On the Data Sheet select your store number from the drop down list.
    4. On the January tab, select the shifts that the managers will be working that week.
    http://www.4shared.com/file/67908278...Schedule_.html

    In the file I have linked above, I have filled out January Wk 1 with a sample schedule. The ranges e13:g13, e14:f14, and e15:g15 are the scheduling scenarios that I would like to have highlighted red. They can appear at any time and can even span two weeks. I don't know if it is even possible to catch the scenarios that span two week because of the layout of the sheets.

    I really do appreciate the help I have received on this.

    Clayton

  12. #12
    Forum Contributor
    Join Date
    02-16-2008
    Location
    Mansfield, TX
    Posts
    324
    Help!!! I really need some formal instruction on this stuff.

  13. #13
    Valued Forum Contributor
    Join Date
    09-19-2008
    Location
    It varies ...
    MS-Off Ver
    Office365 - 64bit
    Posts
    862
    ... sorry - I can't access this site from this machine (blocked site); if you don't have an answer by tonight, I'll take a look from my other machine.

+ 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. Count cell values based on text colour
    By mundo in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-22-2008, 09:25 AM
  2. Color Range Based On Number Range
    By ajocius in forum Excel General
    Replies: 1
    Last Post: 11-24-2006, 12:49 AM
  3. Changing text to upper case
    By RCH in forum Excel General
    Replies: 2
    Last Post: 10-23-2006, 12:04 AM
  4. Getting range of cells with specific text colour, then using in a COUNTIF formulae
    By cheekyflash in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-11-2006, 08:58 PM
  5. formating text and background colour
    By pitway in forum Excel General
    Replies: 1
    Last Post: 09-14-2006, 02:42 PM

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