Shade the last 3 days in color .. help!

Carman - Jul 19, 2010 at 10:31 PM
 Carman - Jul 21, 2010 at 02:22 AM
I have got a range of date in one column, which i want to shade the last 3 days in blue and the rest in yellow.

e.g. If the date is from 1/7/10 to 10/7/10, i want to shade the (entire) rows with date 8/7/10,9/7/10 and 10/7/10 in blue and the rest 1/7/10-7/7/10 in yellow. (Date in column K)

I need VBA script to perform the job so that it can identify the date by itself and capable of future update.

Attached please find the reference.
( in this example, i will want the row (from F to N) of 17/7,18/7 and 19/7 to be shaded in blue and the rest row (also from F to N) in yellow)


2 replies

rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Jul 20, 2010 at 07:27 AM
is this same as your last question on the subject or a different one ?
difference. Because for this one, its not about shading the color of the weekdays( which i could do that by conditional formatting as you suggested), instead i need to figure out how to shade the color of cells which depends on whether its the last 3 days of a range of date( not necessary within a month).
i know it sound familiar, but i guess this one is a bit more challanging as the range of date is " irregular".
in addition, what i need is nothing related to the difference between today's date and the date in the cell. i only need excel to find the last 3 date in a range of date and then shade those rows in colour. Thanks.
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Jul 20, 2010 at 10:16 PM
You can use conditional format using this formula

=OR($C1=LARGE($C:$C,1),$C1=LARGE($C:$C,COUNTIF($C:$C,LARGE($C:$C,1))+1),$C1=LARGE($C:$C,COUNTIF($C:$C,">=" & LARGE($C:$C,COUNTIF($C:$C,LARGE($C:$C,1))+1))+1))

in this C column has the date. The issue with you sample data is that date in your sample is text that only looks like a date. If you can have this in date format ( you can still have it formatted as yyyy-mm-dd but let it be a date) you can use the above formula
Thanks for your reply.
However, after i changed the date format by "Data>Text to columns>Next>Next>Column Data Format>Date>YMD and Finish" and applied the above code (after selecting column F to M) in conditional formatting, none of the cell got shaded. *I did change all the "C" to "K" in the code*
And i would like to know if the above code is designed for changing the rows of the last 3 date?Then what can i do to the rest( those that i need to change to yellow)?

Thank you very very much.
I just tried again... THE CODE WORKS!!