Coloring the last 3 dates in a range of date

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..

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.

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!

apart from that, for some unknown reasons, excel seems not being able to count all the cells.. (e.g. in cell 102, 18/7, gives the number 2 instead of 1) .. what can i do to complete this final step? Thanks!

i just solved the 2nd part of the questions (counting all cells),But excel still cannot detect 17/8 and give the number 1 in the additional column even though it can successfully give 17/8 the blue colour i want!Please help!

problem solved.. THANKS AGAIN

Even though I appreciate that you having courtesy to inform that your issue was resolved, it would have been far nicer had you also mentioned what you had to do to make it work and had your final solution posted, so that some one else may benefit too.