Quick and Simple Cell Shading Code needed

Hi All,

It's been about a year since i've used VBA and i need a quick piece of code that will Fill/Shade any cell in my worksheet Red (#FF0000) only IF the date is equal to Today or less...(I need to highlight expired cells)...

One little catch !!

Im in Australia, and i know there's an issue between the Aus v US date in Excel...i believe there's a piece of code that can rectify this...

Any help would be appreciated...Thanks All

Cheers - Darren

Hello Darren,

This macro should work. The problem occurs when checking dates by their date formats. This code is internationally aware and doesn't compare using formats, only the stored internal values. It will change all cells that are dates red, if the value is equal to today's date or before. Place this code in a Standard VBA module.

Please Login or Register  to view this content.

Sincerely,
Leith Ross

hi,

why not just use conditional formating
steve

hi leith,

if you change a date after you run the macro does it update it when the macro is run again ?

steve

Hello Steve,

In response to your first question, the conditional formats would have to added to each cell anyway using the same basic loop. Adding conditional formats using macros is not as simple as it is by hand, and is quite involved. This process is faster.

With regard to your second question, I am not sure I understand what you're asking. When the macro is run it compares any dates it finds on the worksheet to today's date. If the date is equal to today's date or earlier, the cell is colored red.

Sincerely,
Leith Ross

hi leith


what i was saying is when you run the macro it as you said changes the colour if you made a mistake with a date and change it into the future it still says red even when the macro is re-run

steve

Thanks Leith...works perfectly...

After analysing the data, i now need to add an additional condition into the code...

It's highlighting any of the cells that are <Today Red which is perfect, but now i need to highlight those cells that fall within the next 18 months (from tomorrow + 548days)...these cells need to be Purple (ColorIndex = 3)...

I've had a play around with the code but i cant get it to work...the main problem i have is even if i get the new colour shading to work, it also applies the colour to those cells that should remain white (cells that are beyond 18 months)...how do i stop this from happening ??

Thanks for your help...

hi

try this alter "due"date to suit

Please Login or Register  to view this content.

hope this helps for now

steve

Thanks Steve,

I've run it but it's color coding all of the white cells Purple, as well as any of the cells with text in it...i need to make sure it only picks up the cells with a 'Date' in it...how do i stop this from happening ??

Also...Where you listed:

Please Login or Register  to view this content.

Would it be possible to display it as:

Please Login or Register  to view this content.

That way i wouldnt have to change the date next time i use the code - is this possible ??

hi,

yes change to

Please Login or Register  to view this content.

i will look at the other cell with text in
i nly tried dates in column a

steve

Cheers Steve...

I've attached a small amount of sample data that you may find useful...

Darren

hi,
sorry for the delay work nights
try this it workedon your sample

Please Login or Register  to view this content.

steve

Thanks Steve,

It's now only color coding those cells with a Date in it (not the text fields)...Thanks...

But for some reason the Purple (18 month) code still shades those cells that are beyond 18 months from today (eg: a date in 2010 should be white, but is now purple)...

I've had this problem in the past, and i think we got around it by applying a 3rd condition - Color Cells >18 Months White (i've added in the code and listed it below)...but this is not working, all of the cells with a date in it (other than the Red Cells) are Still Purple...any suggestions to fix this problem ??

The only other thing that comes to mind is the Aus vs US Date problem...not sure if this is what is causing the problem ??

Please Login or Register  to view this content.

I've also tried it with the Due = (Now() + 548) as well as Due = 23/07/2009 but it didnt matter either way.

Cheers - Darren

trythis

Please Login or Register  to view this content.

steve

Works Perfectly !!

Thanks for all your help with this guys...appreaciate it

Cheers - Darren

(PS - So Steve, is it simply a matter that the Code to shade the Cells White, needs to be actioned by VBA before the Code that shades it Purple ??)

hi,

glad we got there in the end-

yes it looksfor the date greater that now plus 548 first then if it is not greater it loks for the greater than now

i think.!! i just worked with the macro leith did

steve