Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Oct 2015
    Posts
    3
    Thanks
    1
    Thanked 0 Times in 0 Posts

    How to set up a colour code system using excel

    Hey, I have a question in relation to setting up a colour code system on excel.
    So basically I have a date (in the past) that an activity took place. The activity will have to take place again a year later (365 days). The spreadsheet may have over 100 rows so is the any way of highlighting the rows in terms of 9 months, 6 months, 3 months, 2 months, 1 month and overdue. In all I would need 6 colour gradients/ steps. Can this be done?


    Example.xlsx

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Ianm,

    Welcome to the Lounge as a new poster!

    You can accomplish this with conditional formatting. I've provided you with the first 3 rules to give you a start, you should be able to complete the process, if not post back.
    conditionalfmt.JPG

    Test File: Time Color Example.xlsx

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,631
    Thanks
    115
    Thanked 645 Times in 589 Posts
    Ianm,

    Note how RG ordered the rules from the longest period to the shortest period. You must keep that order. The "Stop if True" checkboxes are not really necessary.

    Maud

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Quote Originally Posted by Maudibe View Post
    Ianm,
    Note how RG ordered the rules from the longest period to the shortest period. You must keep that order. The "Stop if True" checkboxes are not really necessary.
    Maud
    Maud,

    They do make the spreadsheet more efficient (not checking unnecessary rules). HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. The Following User Says Thank You to RetiredGeek For This Useful Post:

    Ianm2431 (2015-10-27)

  6. #5
    New Lounger
    Join Date
    Oct 2015
    Posts
    3
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks for the help. To make this more complicated, is it possible to make a change of colour be highlighted by an email to an outlook account?
    So if the activity changes from being 6 months to 4 months, could i get the program to generate an email highlighting this?

  7. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Ianm,

    This is doable but much more complicated. You would have to save the current state of the worksheet every time and then have macros to compare the saved state against the current state when the worksheet is opened again. Since you will have to reopen the workbook anyway the email seems a waste of time at least to me.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Tags for this Thread

Posting Permissions

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