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