Hi, you can try like this with the "using a formula" method of conditional formatting.
The formula iswhich is applied toExcel Formula:=LEFT(LOOKUP(2,1/($E$5:E$5<>""),$E$5:E$5),1)="S"
Excel Formula:E7:BB19
Book3
A B C D E F G H I J K L M N O P Q R S T U V W X Y Z AA AB AC AD AE AF AG AH AI AJ AK AL AM AN AO AP AQ AR AS AT AU AV AW AX AY AZ BA BB 1 2 JAN First Date 01/01/2021 Last Date 31/01/2021 3 4 In Out W N OT In Out W N OT In Out W N OT In Out W N OT In Out W N OT In Out W N OT In Out W N OT In Out W N OT In Out W N OT In Out W N OT 5 Sr. No. Name id gen Fri Sat Sun Mon Tue Wed Thu Fri Sat Sun 6 01/01/2021 02/01/2021 03/01/2021 04/01/2021 05/01/2021 06/01/2021 07/01/2021 08/01/2021 09/01/2021 10/01/2021 7 1 ALB 1 8 2 BS 11 9 3 DFRT da215 10 4 ERTY 25 11 5 UYTR 58 12 6 FGHTR 98 13 7 GHIK 112 14 8 Harneet Kaur 369 15 9 LKK 250 16 10 TKK 589 17 11 KK 28 18 12 LL 33 19 13 MM 366 Sheet1
Cell Formulas Range Formula M2 M2 =DATEVALUE("1"&C2) R2 R2 =EOMONTH(M2,0) E5,AX5,AS5,AN5,AI5,AD5,Y5,T5,O5,J5 E5 =TEXT(E6,"ddd") E6 E6 =M2 J6,AX6,AS6,AN6,AI6,AD6,Y6,T6,O6 J6 =IF(E6<$R$2,E6+1,"")
Cells with Conditional Formatting Cell Condition Cell Format Stop If True E7:BB19 Expression =LEFT(LOOKUP(2,1/($E$5:E$5<>""),$E$5:E$5),1)="S" text NO
Hi, you can try like this with the "using a formula" method of conditional formatting.
The formula iswhich is applied toExcel Formula:=LEFT(LOOKUP(2,1/($E$5:E$5<>""),$E$5:E$5),1)="S"
Excel Formula:E7:BB19
Book3
A B C D E F G H I J K L M N O P Q R S T U V W X Y Z AA AB AC AD AE AF AG AH AI AJ AK AL AM AN AO AP AQ AR AS AT AU AV AW AX AY AZ BA BB 1 2 JAN First Date 01/01/2021 Last Date 31/01/2021 3 4 In Out W N OT In Out W N OT In Out W N OT In Out W N OT In Out W N OT In Out W N OT In Out W N OT In Out W N OT In Out W N OT In Out W N OT 5 Sr. No. Name id gen Fri Sat Sun Mon Tue Wed Thu Fri Sat Sun 6 01/01/2021 02/01/2021 03/01/2021 04/01/2021 05/01/2021 06/01/2021 07/01/2021 08/01/2021 09/01/2021 10/01/2021 7 1 ALB 1 8 2 BS 11 9 3 DFRT da215 10 4 ERTY 25 11 5 UYTR 58 12 6 FGHTR 98 13 7 GHIK 112 14 8 Harneet Kaur 369 15 9 LKK 250 16 10 TKK 589 17 11 KK 28 18 12 LL 33 19 13 MM 366 Sheet1
Cell Formulas Range Formula M2 M2 =DATEVALUE("1"&C2) R2 R2 =EOMONTH(M2,0) E5,AX5,AS5,AN5,AI5,AD5,Y5,T5,O5,J5 E5 =TEXT(E6,"ddd") E6 E6 =M2 J6,AX6,AS6,AN6,AI6,AD6,Y6,T6,O6 J6 =IF(E6<$R$2,E6+1,"")
Cells with Conditional Formatting Cell Condition Cell Format Stop If True E7:BB19 Expression =LEFT(LOOKUP(2,1/($E$5:E$5<>""),$E$5:E$5),1)="S" text NO
LOOKUP(2,1/($E$5:E$5<>""),$E$5:E$5)
returns the last populated cell in row 5 from column E on-wardsLEFT(...,1)
and see if it is a "S".LOOKUP(REPT("Z",255),$E5:E5)
Seems you are a Gem Of a Person.Hi,
This partLOOKUP(2,1/($E$5:E$5<>""),$E$5:E$5)
returns the last populated cell in row 5 from column E on-wards
To see if that day is a weekend we can look at the 1st characterLEFT(...,1)
and see if it is a "S".
But thinking about it, it would probably be more efficient to replace the LOOKUP() part with this.LOOKUP(REPT("Z",255),$E5:E5)
If you do a search on the internet for lookup(rept("z",255 you'll get lots of hits explaining what is going on.
Great Help Form R. Your formula worked for me. Great Work & Expertise. Also, for my understanding would request please can you please unfold the formula bit by bit for my deep understanding.
Once again your solution is Marvelous. Great Regards
In the existing formula as suggested by you , if only 2nd & 4th Saturdays of every month to be conditionally formatted to "RED" with rest of the things as it is. How to modify, please help ! Thanks.
Can you post some sample data that demonstrates the problem?
=AND(LEFT(LOOKUP(REPT("z",255),$E$5:E$5,$E$5:E$5),1)="S",ISEVEN(SUMPRODUCT(0+(TEXT($E$6:E$6,"YYYYMM")=TEXT(LOOKUP(9E+99+307,$E$6:E$6,$E$6:E$6),"YYYYMM")),0+($E$5:E$5=LOOKUP(REPT("z",255),$E$5:E$5,$E$5:E$5)))))