Cell O5,P5, Q5,R5 & S5 are merged and contains one value. I need O7 to S9 to be conditionally formatted based on the value of Cells O5 TO S5. Request

[anildubey]

Cell O5,P5, Q5,R5 & S5 are merged and contains one value.
I need O7 to S9 to be conditionally formatted based on the value of Cells O5 TO S5.
Request please help urgently

 

[anildubey]

Hi, you can try like this with the "using a formula" method of conditional formatting.

The formula is

=LEFT(LOOKUP(2,1/($E$5:E$5<>""),$E$5:E$5),1)="S"

which is applied to

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 is

=LEFT(LOOKUP(2,1/($E$5:E$5<>""),$E$5:E$5),1)="S"

which is applied to

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

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

 

[FormR]

Hi,

This part LOOKUP(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 character LEFT(...,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.

 

[anildubey]

Hi,

This part LOOKUP(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 character LEFT(...,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.

Seems you are a Gem Of a Person.
Thanks for all kind help & enlightening me in improving my understanding on the Subject.
Billion Kudos !

 

[anildubey]

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.

 

[FormR]

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?

 

[anildubey]

Can you post some sample data that demonstrates the problem?

The data file is same as posted on this thread on January,12 2021 Sir,
Please Help !

 

[FormR]

Hi - you could try this lengthy formula with your CF.

=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)))))