# Coloring the last 3 dates in a range of date

Solved/Closed
Carman - Jul 21, 2010 at 10:32 PM
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Jul 30, 2010 at 08:33 AM
Hello,
In my last post, i mentioned about coloring the last 3 dates in a range of date in blue, and the rest in yellow.
I used this code as to do the coloring:

----
Columns("F:M").Select
Selection.FormatConditions.Delete
"=OR(\$K1=LARGE(\$K:\$K,1),\$K1=LARGE(\$K:\$K,COUNTIF(\$K:\$K,LARGE(\$K:\$K,1))+1),\$K1=LARGE(\$K:\$K,COUNTIF(\$K:\$K,"""">="""" & LARGE(\$K:\$K,COUNTIF(\$K:\$K,LARGE(\$K:\$K,1))+1))+1))"
Selection.FormatConditions(1).Interior.ColorIndex = 34
Formula1:= _
"=OR(\$K1=LARGE(\$K:\$K,1),\$K1=LARGE(\$K:\$K,COUNTIF(\$K:\$K,LARGE(\$K:\$K,1))+1),\$K1=LARGE(\$K:\$K,COUNTIF(\$K:\$K,"""">="""" & LARGE(\$K:\$K,COUNTIF(\$K:\$K,LARGE(\$K:\$K,1))+1))+1))"
Selection.FormatConditions(2).Interior.ColorIndex = 36
----

However, only the last 2 dates are being selected and shaded in blue.What can i do the make excel shade the last 3 dates?

Thanks!
Related:

rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 767
Jul 22, 2010 at 02:56 PM
Have you tested it using manually conditional format.
Yes. It works when i use manually conditional format. But when i use macro to record the process and press it to the VBA writer, only last 2 dates got selected..
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 767
Jul 23, 2010 at 04:43 AM
upload the workbook (along with sample data and your macro BOTH) at some same share site and post the link here
https://authentification.site/files/23525416/Book1.xls
This is the refererence file ( with macro inside)
In this example, only the last 2 dates (18/7 and 19/7) got selected but not (17/7-20/7).
What can i do the make it work?
Thanks.
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 767
Jul 26, 2010 at 07:36 AM
Why you need two conditons. You have a cylic conditons here.
You are saying for column F:M, that be of yellow color if the formula is true
and right after you are agains saying, that F:M should be blue if the formula is true. You need to add only one to identify the last three dates. Now if you want to color last two dates in one color and the third date in another, you need to fix the formula to do so

Here is the break down

"=OR(

this part gives you the last date in column K
\$K1=LARGE(\$K:\$K,1),

This part gives you the 2nd most latest date in column K
\$K1=LARGE(\$K:\$K,COUNTIF(\$K:\$K,LARGE(\$K:\$K,1))+1),

This part gives you the 3rd most latest date in column K
\$K1=LARGE(\$K:\$K,COUNTIF(\$K:\$K,"""">="""" & LARGE(\$K:\$K,COUNTIF(\$K:\$K,LARGE(\$K:\$K,1))+1))+1)

)"

The OR part is bascially saying if any if the three condition is true, return a true
Thank you so much for your clear explanation.
However after i successfully sort out the last 3 dates with color, i am now trying to do color sorting by the IF function in an additional column.
However, excel actomatically converted the above code you provided into :

OR(R[-1]C11=LARGE(C11,1),R[-1]C11=LARGE(C11,COUNTIF(C11,LARGE(C11,1))+1),R[-1]C11=LARGE(C11,COUNTIF(C11,"""">="""" & LARGE(C11,COUNTIF(C11,LARGE(C11,1))+1))+1))

So the IF function becomes:
ActiveCell.FormulaR1C1 = _
"=IF(OR(R[-1]C11=LARGE(C11,1),R[-1]C11=LARGE(C11,COUNTIF(C11,LARGE(C11,1))+1),R[-1]C11=LARGE(C11,COUNTIF(C11,"""">="""" & LARGE(C11,COUNTIF(C11,LARGE(C11,1))+1))+1)),1,2)"

I used the IF function as to give a true/false value which can alow me to the sorting of the two colours. (Target : last 3 dates = blue = true =1 and rest dates=yellow=false=2)

The color shading part works but the numbering only works for 18/7 and 19/7. ( suppose to give 17/7 the same number as well)
Please have a look at this reference file:
https://authentification.site/files/23540171/Book2.xls

Thanks again!