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
Selection.FormatConditions.Add Type:=xlExpression, 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(1).Interior.ColorIndex = 34
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlNotEqual, _
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!

1 reply

rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
769
Jul 22, 2010 at 02:56 PM
Have you tested it using manually conditional format.
0
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..
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
769
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
0
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.
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
769
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
0
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!
0