+ Reply to Thread
Results 1 to 18 of 18

VBA or Conditional Formatting (More than 1 Argument)

  1. #1
    Registered User
    Join Date
    03-10-2012
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    79

    Question VBA or Conditional Formatting (More than 1 Argument)

    Hi All,

    This may be simple for someone, I tried for days using Conditional formatting to highlight cells based on specific cells values.

    I have roster made up of 8 employees, grouped in 4 teams. To help staff visually see what they are working, I highlighted their shifts in 4 colors. I have given each team a Named Range to help with the formulas. At the moment I have conditional formatting which is great. The problem is happening when a staff member goes on leave or sick, another employee covers them.

    I can't work out with conditional formatting formula, the relief employee covering the leave or sick with their team color. I have attached a example. In the example please see August roster.
    * I have Employee 1 on Rec Leave from 18 Aug - 21 Aug and Employee 7 is covering the leave. I would like Employee 7 color to be "Yellow" to show his working for Team 1 not Team 4.
    * I have Employee 3 sick on 14 Aug - 15 Aug and Employee 1 & 2 are covering. Employee 1 & 2 colors should be "Red".

    I also looked into Interior.ColorIndex in VBA. There are 56 colors to choose from which is great. At moment, still playing with code to work but unsuccessful. :(

    Any help be appreciated.

  2. #2
    Forum Expert
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

    Re: VBA or Conditional Formatting (More than 1 Argument)

    Hi Bloodywog,

    I like the look and feel of your spreadsheet. Please see the code that follows and the attached file (in Excel 2003 format). You should be able to use the macro in your spreadsheet, after you remove conditional formatting. Please note that CONDITIONAL FORMATTING takes precedence over other colors.

    I had problems with employee substitution, until I made a small change to your symbols. If someone is substituting for Employee 5 on the night shift, I changed the designation from 'N' to 'N5'. After I did that, it was relatively easy using VBA to color your roster.

    Here is how I approached your problem:
    a. All conditional formatting was removed.
    b. There are colors in Column 'A' for each employee (cells A3 thru A10 are the MASTER COLORS).
    c. There are colors for SL, RL, and Error in cells A53 thru A55.

    Lewis

    Code follows:
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    03-10-2012
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    79

    Re: VBA or Conditional Formatting (More than 1 Argument)

    Hi LJMetzger

    First I like to say is thanks for replying and helping. I have been waiting for help for long time. I never knew someone replied, I didn't get an email or notification from the forum until I checked today.
    Since last time, I have done some playing around.

    a) I created a new sheet (RecLeave) to insert all the data in. There is a macro to insert the data to the Roster.
    b) I also removed all conditional formatting to make your VBA work.

    Using the example attached as an example, Do you think we can modify the VBA code to process the leave (RecLeave) tab and also include yours? See code:
    Please Login or Register  to view this content.
    Your version of the roster works really well, especially when you change the designation from 'N' to 'N5' (substituting for Employee 5 on the night shift). Ideally I like the roster to only show "D" or "N" with their team colours.

    a) Do you think we can modify the VBA code so when "Employee 2" takes leave from 9 Jan 2015 until 28 Jan 2015, the substitute is "Employee 4". Would it help if we had Team 1 for "Yellow", Team 2 for "Red", Team 3 for "Green" and Team 4 for "Purple"?

    b) I have added an additional staff for each team (12 employees). I can't get your VBA to work Rec Leave ExcelForum Example.xlsm

  4. #4
    Forum Expert
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

    Re: VBA or Conditional Formatting (More than 1 Argument)

    Hi,

    Try the attached file.

    The 'Process Leave' Button process the leave AND Colors the Roster Sheet at the same time.
    The 'Color Employee Roster' Button Colors the Roster Sheet Only.

    This is what I did:
    a. Reworked Macros to be valid for any number of Employees and any number of months.
    b. The Number of Employees is determined by the List in Column 'AN'.
    c. There MUST be the same number of employees for each month in the same order.
    d. Team Id on the Rec Leave Sheet is NOT NEEDED.
    e. You can replace the 'Posted Roster' Date with an 'X' is you like. I prefer the date.

    Your version of the roster works really well, especially when you change the designation from 'N' to 'N5' (substituting for Employee 5 on the night shift). Ideally I like the roster to only show "D" or "N" with their team colours.
    The values can be changed, but the Sheet can NEVER be colored again and get the correct colors with the current design. The only way I can see to have 'D5' turn into 'D' is to save the values somewhere, when the Sheet is colored, similar to what is done with 'Leave Requests'. It seems llike a lot of work, for a very small reward.

    Lewis

  5. #5
    Registered User
    Join Date
    03-10-2012
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    79

    Re: VBA or Conditional Formatting (More than 1 Argument)

    Hi,

    Thanks for the updated attachment. Looks great. Really appreciated it.
    • The values can be changed, but the Sheet can NEVER be colored again and get the correct colors with the current design. The only way I can see to have 'D5' turn into 'D' is to save the values somewhere, when the Sheet is colored, similar to what is done with 'Leave Requests'.
      Is there a way to run a VBA code on change (Roster) to rename the "N5" or "D1" to "N" or "D" changes without interfering with the color change code you did similar to Leave Requests"??
    • How much trouble would it be to copy the data vales from roster somewhere then when the Sheet is colored, similar to what is done with 'Leave Requests' like you suggested? It may seem like a "small" reward but visually it's ideal. The roster will contain more data in future.

  6. #6
    Forum Expert
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

    Re: VBA or Conditional Formatting (More than 1 Argument)

    You just gave me a semi-brilliant idea. We could have two rosters in the same workbook.
    a. The REAL ROSTER that can be hidden and nobody sees with the REAL CODES.
    b. The VISUALLY PLEASING ROSTER that contains PrettyPrint.

    There is a potential dangerous side effect. When making changes to the roster, you MUST make changes only to the REAL ROSTER.

    I will get back to you in a few days with an update that contains the changes as mentioned above.

    Lewis

  7. #7
    Forum Expert
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

    Re: VBA or Conditional Formatting (More than 1 Argument)

    Try the attached file, which creates the two Rosters.

    Please be careful when using the Workbook:
    a. Only Edit the 'RealRoster' as this is the MASTER COPY. DO NOT EDIT the 'Visually Pleasing Roster'.
    b. Remember, the Leave Requests are only processed ONCE. To process the 'Leave Requests again', remove the values from the 'Posted Roster' Column, Column 'I'.

    If you don't double check these items each time you make a new copy public, you will probably have several very angry employees.

    Lewis

  8. #8
    Registered User
    Join Date
    03-10-2012
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    79

    Re: VBA or Conditional Formatting (More than 1 Argument)

    LJMetzger

    That's amazing. Really like it.

    Works like a charm.

    Just a quick question, when I close the spreadsheet down a error pops up "The picture is too large and will be truncated". Do you know why?

  9. #9
    Forum Expert
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

    Re: VBA or Conditional Formatting (More than 1 Argument)

    The problem does not appear in Excel 2003, but I duplicated your problem in Excel 2010.

    The problem is caused by the Defined Names that you had in the original workbook,which I did not use. If you delete them, the problem goes away.

    Formulas > Name Manager > Select all the names with the mouse > Delete
    Save the workbook

    The next time you open the workbook and try to save the workbook, the problem should disappear.

    If you want to keep the Defined Names, delete the ones that aren't needed, and redefine the ranges for the ones you want to keep.

    I'm not a big fan of 'Named Ranges', except in rare instances where they make formulas or VBA easier to use and maintain.

    Lewis

  10. #10
    Registered User
    Join Date
    03-10-2012
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    79

    Re: VBA or Conditional Formatting (More than 1 Argument)

    Thanks.

    All works well. Removed the nameranges and all sweet.

    Before I close this thread, I tested the sheet again but instead of Employee 1 etc, i replaced it with their "Surnames". When process leave your "DATA INTEGRITY" message box appears.. Do I remove that part of the VBA code in "ProcessLeave"?

  11. #11
    Forum Expert
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

    Re: VBA or Conditional Formatting (More than 1 Argument)

    Before I close this thread, I tested the sheet again but instead of Employee 1 etc, i replaced it with their "Surnames". When process leave your "DATA INTEGRITY" message box appears.. Do I remove that part of the VBA code in "ProcessLeave"?
    No. The code in the 'Leave Section' is based on the 'Employee Number'. I will need a few days to change the code so it can use Names. That will include the employee being in a different order in the list on different months.

    Lewis

  12. #12
    Registered User
    Join Date
    03-10-2012
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    79

    Re: VBA or Conditional Formatting (More than 1 Argument)

    Ok. I just went through your code and realised what you done. Sorry Lewis, employee number was for forum purposes.

    Before you put extra work into it will send new attachment with just few more little adjustments.

  13. #13
    Registered User
    Join Date
    03-10-2012
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    79

    Re: VBA or Conditional Formatting (More than 1 Argument)

    Lewis

    See attached file.

    Again sorry for confusion. Only used Employee number for the purpose of the forum.

    • RecLeave - Renamed the Sheet. "Leave". I will include multiple leave types i future. E.g. Rec Leave, Sick Leave, Long Service, Training etc. Easy to manage.
    • RecLeave - Instead of Time stamp can you put "X"? Maybe leave time stamp code there as note in case I change it later.
    • RecLeave - Notes Column - Just to remind me what, why etc.
    • RealRoster - May show 2 years worth of roster. Does code continue down if I put it until Dec 2016?
    • RealRoster - More AL: More special colors. Partially inserted them in VBA already

    Many thanks
    Attached Files Attached Files

  14. #14
    Forum Expert
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

    Re: VBA or Conditional Formatting (More than 1 Argument)

    Hi,

    I'm working on your most recent request. As I was doing it, I realized that by replacing Employee Numbers with real names, we have a new/old problem:

    Your version of the roster works really well, especially when you change the designation from 'N' to 'N5' (substituting for Employee 5 on the night shift). Ideally I like the roster to only show "D" or "N" with their team colours.

    If we put 'N5' into a cell, what does the '5' now refer to, since there is NO Employee 5 any more.

    I was hoping that with your 'Leave' Sheet request, that there could be any number of employees for any month, and in any order. That is another complication of not having the Number '5'.

    I have no idea how to handle this problem, unless you assign a number to each employee each month. So Column 'AN' would go on forever, with a helper column next to it with Employee Number for that month.

    Please let me know what you want to do.

    Lewis

  15. #15
    Registered User
    Join Date
    03-10-2012
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    79

    Re: VBA or Conditional Formatting (More than 1 Argument)

    Hi,
    • I was hoping that with your 'Leave' Sheet request, that there could be any number of employees for any month, and in any order.
      There can be any number of employees for any month and in any order.
    • If we put 'N5' into a cell, what does the '5' now refer to, since there is NO Employee 5 any more.
      We don't need to have numbers after employee name. I though you had problems with employee substitution and be easier to identify them. E.g. If someone is substituting for Employee 5 on the night shift, we just need to get the relief employee to cover that employee and color the roster to match. The last post worked beautifully but with the employees surnames.
    • unless you assign a number to each employee each month. So Column 'AN' would go on forever, with a helper column next to it with Employee Number for that month.
      Why don't we created a new sheet called "Data" or "Engine" to store the information. The colors in Column 'A' for SL, RL, CS, OT, TR & LS and Error, Column B for list of Employees and their team color. We can include a helper column (Column C) next to it with Employee Number for that month if you think it help.
    • Maybe in "Leave" sheet we can have a column for Employee numbers 1-4 = Yellow, 5-8 = Red, 9-12 = Green & 13-17 = Purple. Whatever way is easy.

    Let me know what you think.
    Last edited by Bloodywog; 01-30-2015 at 08:02 PM.

  16. #16
    Forum Expert
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

    Re: VBA or Conditional Formatting (More than 1 Argument)

    I will use your suggestions, and try to come up with a solution that minimizes the work you have to do. It will probably take several days.

    Lewis

  17. #17
    Forum Expert
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

    Re: VBA or Conditional Formatting (More than 1 Argument)

    Hi,

    Try the attached file. I originally made things more difficult than they had to be. I simplified things and that allowed more flexibility in the data and made it easier to implement your 'Leave Requirements. I apologize for the delay, but it's hard for me to say no, and I was attempting to juggle 4 or 5 files simultaneously (at the same time). And that doesn't usually end up with a positive outcome. I was able to take my time on your file, and I think do things correctly.

    Please let me know if you need any corrections and/or changes.

    Major features and changes:
    a. Completely reworked all the code.
    b. Sheet Names are now:
    (1) OrigRoster - which contains your original schedule ONLY. It should NEVER contain shift changes or Leaves unless the changes are PERMANENT.
    (2) Roster - contains the OrigRoster modified for Leave and Shift Changes.
    (3) Leave - contains Leave Requests and Shift Changes.
    c. There can be any number of months (and years) and any number of employees per month.
    d. Colors for codes come from 'OrigRoster' Column 'AL'. I put in some extra codes to make it easier for you to expand in the future.
    See Sub GenerateGlobalColorList() if you change the order of the colors and/or add colors.
    e. Colors for employees (teams) come from Column 'A'.
    f. Leave Sheet has the following Special Codes :
    (1) Relief Employee can be BLANK (nothing entered) and Leave Request will be processed with NO RELIEF EMPLOYEE.
    (2) Relief Employee can be 'None' (case insensitive) and Leave Request will be processed with NO RELIEF EMPLOYEE.
    (3) Leave Code can be 'SWITCH' (case insensitive) and the two employees will switch shifts.
    g. Leave can be for any continuous duration (e.g. Jun 27, 2015 to Jan 23, 2016).
    h. All LEAVE requests are processed every time, even if there is an 'X' in the 'Posted Roster' column.
    i. You might want to put a copy of Column 'A' in Column 'AG' on Sheet 'OrigRoster'.

    Lewis

  18. #18
    Registered User
    Join Date
    03-10-2012
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    79

    Re: VBA or Conditional Formatting (More than 1 Argument)

    Wow. That's impressive Lewis.

    I really appreciate all the work you done and going out of your way. Worth the wait.

    Works really well! Thanks again

+ 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. [SOLVED] Countif with a conditional argument
    By extrain in forum Excel General
    Replies: 3
    Last Post: 04-02-2012, 05:13 AM
  2. Can I Put a Conditional Argument into a string?
    By jerseyguy1996 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-23-2009, 07:10 PM
  3. Can I HIDE cells with a conditional argument?
    By Bob the Builder in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-22-2005, 06:05 AM
  4. Function (array argument, range argument, string argument) vba
    By Witek in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-24-2005, 11:07 AM

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