Results 1 to 5 of 5

Thread: Highlight cells of a training schedule if training start/end dates filled

  1. #1

    Highlight cells of a training schedule if training start/end dates filled



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi All,

    I'm customizing a calendar template to use it as a training schedule.

    I want days in the calendar to be highlighted in certain colors when training information (mostly start/end date & status) are filled in ("training list" tab) through conditional formatting or other solution.

    I already have it working for Holidays & priority need to be kept for holidays over the training if 1 is scheduled at the same time.

    Any idea to also showcase the customer, training name & instructor in the schedule would be awesome.

    Thx in advance.

    PS : sample attached
    Attached Files Attached Files

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,189
    Articles
    57
    Blog Entries
    14
    Hi there, and welcome to the forum!

    I have a calendar that I use for planning our month ends that is quite similar to this. My suggestion here would be that your "List of trainings" actually be built on a day by day basis, rather than date range. From there, you could pull this into summary tables (like you have with holidays on the front page) for "confirmed" or "soon to be..."

    The next step would be to set up a conditional formatting rule set that checks the date vs each table (using Stop If True in the cf rule to control the order)
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Thank you Ken.
    Sorry i was on a business trip.

    Let me try this & i'll let you know how it works.
    By the way what if i have 2 trainings in the same date ? Do you think there could be a way to have a gradient ?
    In parallel i guess there's no way to put customer & instructor name in the calendar.

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,189
    Articles
    57
    Blog Entries
    14
    Hi there,

    Sure, you could have a gradient. Gradient fills are available, so it's just making a rule to count how many items are on each date.

    With regards to putting in both customer and instructor, why not? Join the text in advance and use a line break:
    =A1&CHAR(10)&B1

    Set the cell holding that formula to wrap text and it will show on two lines.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  5. #5

    Solved - Highlight cells of a training schedule if training start/end dates filled

    Hi Ken,

    I made some testing, it looks fine. Just need some work to fine-tune everything & make it clear.
    Thx so much for your help.

    Cheers.

Posting Permissions

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