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.



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

Brilliant, Thanks for that!

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.

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

Hi Claus

I have started a developer tab and I'm able to use VBA.

Could you please advise from here?

Thanks

Daniel

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

Thats great, Very Much Appreciated!!

Thanks again

Danny