Formatting

[Rick448]

Hi all, I am struggling to make this do what i would like and wondered if anyone here could help me?

I would like the whole row to change colour if possible. Green for the first 21 days, amber for days 22-28 and red for more than 28. If a date is entered in completed I then want the line to go grey to show it has been completed.Including the job date collum.

I have attached the sheet to help.

Thanks if anyone can assist me with this.

[jwright650]

Question:
Are you wanting the row to turn colors if date in Column "A" is:

(Green)...."0-21 days old",
(Yellow)..."between 22-27 days old",
(Red)......."28 days or older "

.........than today's date?

[Rick448]

Sorry, from the date of the job in the left collum,

[jwright650]

I guess my question is:
How does it know how old that date is (the one on the left), if you don't have a second date to check against?

It can check that date against "today's date" or "any date you choose", if that is what you are trying to do.

[Rick448]

I mean the difference between todays date and the date the job was given and entered in the box. My main problem is getting the row to go grey when a date is put in the date completed box.

[Leith Ross]

Hello Rick448,

This macro will change the row colors like you want. Since you had multiple sheets in the workbook, I assumed you would want to apply this to sheet. If you want to the macro to run on specific sheet or sheets, let me know and I'll adjust it for you.

Please Login or Register  to view this content.

[Rick448]

I want to apply that to all the sheets, where do i put the code in?

Thanks for the help. Awesome place

[Leith Ross]

Hello Rick,

The attached workbook is already set up to apply the formatting to all sheets. The macro is located in the ThisWorkbook module. Here is the event procedure with the macro code...

Please Login or Register  to view this content.

[Rick448]

Thanks for the help everyone. That has saved me a massive amount of work. Totally awesome site.

[jwright650]

Rick,
Just a note....if you want to use the same exact colors that you had in the legend on the right of your sheet, you can change the color indexes in the lines of code...
ie.
Bright Green = 4
Yellow(actually it's Light Orange) = 45
Red = 3

[Rick448]

Rick,
Just a note....if you want to use the same exact colors that you had in the legend on the right of your sheet, you can change the color indexes in the lines of code...
ie.
Bright Green = 4
Yellow(actually it's Light Orange) = 45
Red = 3

Thanks John.