How to set up a colour code system using excel

Topic

New Reply

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?

42418-Example

Reply | Quote

Replies

Ianm,

Welcome to the Lounge as a new poster! :cheers:

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.
42419-conditionalfmt

Test File: 42420-Time-Color-Example

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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

Reply | Quote

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 :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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?

Reply | Quote

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. :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote