Colour Coding dates

[DannyQuinn]

Hi

I'm currently working on a spreadsheet that tracks paperwork for peopl
that my organisation has moved into employment. I'm looking to colou
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 bee
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 Marlet
font) then the cell F1 would turn green.

Please let me know if there is any way to do this or ask fo
clarification if I've not explained this very well.

&#61692

 

[Claus Busch]

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

 

[DannyQuinn]

Hi Danny,

Am Fri, 5 Apr 2013 12:28:31 +0000 schrieb DannyQuinn:
-

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

Brilliant, Thanks for that!

One last query. I now want excel to automatically sort data for me base
on the criteria I have entered. As mentioned above, Column D has a rang
of dates, column E is essentially a checklist to make sure that I hav
picked up such paperwork on the specific dates.

I want excel to autimatically sort column D by font colour and date. S
if I yesterdays date in D1 and todays date in D2, Both coloured red as
have no criteria in E1 or E2, I then enter 'a' in column E1 as I hav
picked up the paperwork for yesterday, it will turn green and I want i
to be put to the bottom of the spreadsheet automatically.

Essentially, I'm wanting all outstanding paperwork (coded red) to b
grouped together and all green paperwork which I have completed to b
arranged to the bottom of the spreadsheet.

Thanks again

 

[Claus Busch]

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

 

[DannyQuinn]

Hi Danny,

Am Mon, 8 Apr 2013 08:53:35 +0000 schrieb DannyQuinn:
-

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

Hi Claus

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

Could you please advise from here?

Thanks

Danie

 

[Claus Busch]

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 here:
https://skydrive.live.com/#cid=9378AAB6121822A3&id=9378AAB6121822A3!191
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

 

[DannyQuinn]

Hi Danny,

Am Tue, 9 Apr 2013 14:40:59 +0000 schrieb DannyQuinn:
-

please look he
http://tinyurl.com/3s4zwaz
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

Thats great, Very Much Appreciated!!

Thanks again

Dann