Menu
  1. Accueil
  2. Tech Forum
  3. Office Software

Coloring the last 3 dates in a range of date [Solved/Closed]

Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 -
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

Answer 1 / 1
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 767
Have you tested it using manually conditional format.
View all 9 comments
Report

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!
Report

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!
Report

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!
Report

problem solved.. THANKS AGAIN
Report
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 767
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.
Ask a question

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!