Shade the last 3 days in color .. help!

Solved/Closed
Carman - Jul 19, 2010 at 10:31 PM
 Carman - Jul 21, 2010 at 02:22 AM
Hello,
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.
https://authentification.site/files/23445407/Book1.xls
( 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)

Thanks!!

2 replies

rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
770
Jul 20, 2010 at 07:27 AM
is this same as your last question on the subject or a different one ?
0
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".
0
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.
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
770
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
0
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.
0
I just tried again... THE CODE WORKS!!
THANK YOU VERY MUCH! YOU ARE GENIUS!!
0