Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Colour Coding dates
Hi
I'm currently working on a spreadsheet that tracks paperwork for people that my organisation has moved into employment. I'm looking to colour code the dates where the paperwork has been due and not submitted. The layout is as follows: Column A: Reference number Column B: Name of the client. Column C: Employer Column D: Date the said client commences employment. Column E: Paperwork submitted (ticked where applicable) Column F: Initial Sustained paperwork. Column G: Paperwork Submitted. (Ticked where applicable) Column H: Secondary Sustained Paperwork Column I: Paperwork submitted (Ticked where applicable). So As an example I would have: T567-John Smith-ASDA-1/1/13-(tick)-1/2/13-(no tick)-1/3/13-(no tick) What I'm looking to do is essentially flag up which dates have been overlooked or have outstanding paperwork. So if cell G1 is blank then I would like cell F1 to turn red. If I was then to insert a tick (in the form of the letter 'a' in Marlett font) then the cell F1 would turn green. Please let me know if there is any way to do this or ask for clarification if I've not explained this very well. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Colour Coding dates
Hi Danny,
Am Fri, 5 Apr 2013 12:28:31 +0000 schrieb DannyQuinn: Column A: Reference number Column B: Name of the client. Column C: Employer Column D: Date the said client commences employment. Column E: Paperwork submitted (ticked where applicable) Column F: Initial Sustained paperwork. Column G: Paperwork Submitted. (Ticked where applicable) Column H: Secondary Sustained Paperwork Column I: Paperwork submitted (Ticked where applicable). So As an example I would have: T567-John Smith-ASDA-1/1/13-(tick)-1/2/13-(no tick)-1/3/13-(no tick) select F1:Fx and choose fill color red. Then with selection =Conditional Formatting = Formula: =G1="a" and format with green fill color. Regards Claus Busch -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#3
|
|||
|
|||
Quote:
One last query. I now want excel to automatically sort data for me based on the criteria I have entered. As mentioned above, Column D has a range of dates, column E is essentially a checklist to make sure that I have picked up such paperwork on the specific dates. I want excel to autimatically sort column D by font colour and date. So if I yesterdays date in D1 and todays date in D2, Both coloured red as I have no criteria in E1 or E2, I then enter 'a' in column E1 as I have picked up the paperwork for yesterday, it will turn green and I want it to be put to the bottom of the spreadsheet automatically. Essentially, I'm wanting all outstanding paperwork (coded red) to be grouped together and all green paperwork which I have completed to be arranged to the bottom of the spreadsheet. Thanks again. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Colour Coding dates
Hi Danny,
Am Mon, 8 Apr 2013 08:53:35 +0000 schrieb DannyQuinn: I want excel to autimatically sort column D by font colour and date. So if I yesterdays date in D1 and todays date in D2, Both coloured red as I have no criteria in E1 or E2, I then enter 'a' in column E1 as I have picked up the paperwork for yesterday, it will turn green and I want it to be put to the bottom of the spreadsheet automatically. Essentially, I'm wanting all outstanding paperwork (coded red) to be grouped together and all green paperwork which I have completed to be arranged to the bottom of the spreadsheet. you can filter by color but not sort. Sort first by column E and then by column D All cells with "a" will be on top and then the data will be sorted by date. If you want to do it automatically you have to use VBA. If it is allowed to use VBA, then send another post. Regards Claus Busch -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#5
|
|||
|
|||
Quote:
I have started a developer tab and I'm able to use VBA. Could you please advise from here? Thanks Daniel |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Colour Coding dates
Hi Danny,
Am Tue, 9 Apr 2013 14:40:59 +0000 schrieb DannyQuinn: I have started a developer tab and I'm able to use VBA. Could you please advise from here? please look he https://skydrive.live.com/#cid=9378A...121822A3%21191 for the workbook "Danny" Right-click and download it, because VBA is not enabled in SkyDrive. If you write an "a" in column E your data will be sorted by color (a) and date in D The code is in Worksheet_Change-event Regards Claus Busch -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#7
|
|||
|
|||
Quote:
Thanks again Danny |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Colour-coding a cell from a drop down list | Excel Programming | |||
Colour coding data | Excel Discussion (Misc queries) | |||
colour coding alternate weeks | Excel Programming | |||
Automatic Colour Coding on a Row | Excel Discussion (Misc queries) | |||
Conditional formatting: Colour coding | Excel Discussion (Misc queries) |