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

 

[FormR]

Hi, you didn't mention the condition so this is just an example using the "with a formula" method.

Book15
	O	P	Q	R	S
5	5
6
7	6	0	20	5
8	3	5	1	2	99
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
O7:S9	Expression	=O7>=$O$5	text	NO

 

[anildubey]

Thanks for your prompt reply. Request, please help in the attached file wherever Saturday & Sunday comes all the columns below the merged cell shall become all "RED".
This should happen for all the SATURDAYS & SUNDAYS in the month and accordingly it should change dynamically on changing the month from cell C2.

Please help & will be obliged dear.

 

[FormR]

please help in the attached file

Hi, I don't see any attachments. Take a look here to see a way to show your example data directly in the thread in way that allows your potential helpers to easily copy the data for testing.

XL2BB - Excel Range to BBCode


.

 

[anildubey]

Hi, I don't see any attachments. Take a look here to see a way to show your example data directly in the thread in way that allows your potential helpers to easily copy the data for testing.

XL2BB - Excel Range to BBCode


.

 

[anildubey]

Thanks for your prompt reply. Request, please help in the attached file wherever Saturday & Sunday comes all the columns below the merged cell shall become all "RED".
This should happen for all the SATURDAYS & SUNDAYS in the month and accordingly it should change dynamically on changing the month from cell C2.

Please help & will be obliged dear.

MREXCEL-AD-08-01-2021.xlsm
	B
1
OT

 

[FormR]

Hi, you need to select/highlight the cells that you want to show before pressing the "capture range" button and pasting here.

 

[Joe4]

As I mentioned in one of the other threads you started on this, there is a Test Here forum you can practice/test using this tool there before posting to this thread, to make sure that you are using it correctly.

 

[anildubey]

Hi, you need to select/highlight the cells that you want to show before pressing the "capture range" button and pasting here.

		JAN								First Date		#######			Last Date		#######
				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
Sr. No.	Name	id	gen	Fri					Sat					Sun					Mon					Tue					Wed					Thu					Fri					Sat					Sun
				01-Jan					02-Jan					03-Jan					04-Jan					05-Jan					06-Jan					07-Jan					08-Jan					09-Jan					10-Jan
1	ALB	1		10:00 AM	7:30 PM	9:30	8:45	0:45											10:00 AM	7:30 PM	9:30	8:45	0:45
2	BS	11		10:00 AM	7:30 PM	9:30	8:45	0:45											10:00 AM	7:30 PM	9:30	8:45	0:45
3	DFRT	da215		10:00 AM	7:30 PM	9:30	8:45	0:45											10:00 AM	7:30 PM	9:30	8:45	0:45
4	ERTY	25		10:00 AM	7:30 PM	9:30	8:45	0:45											10:00 AM	7:30 PM	9:30	8:45	0:45
5	UYTR	58		10:00 AM	7:30 PM	9:30	8:45	0:45											10:00 AM	7:30 PM	9:30	8:45	0:45
6	FGHTR	98		10:00 AM	7:30 PM	9:30	8:45	0:45											10:00 AM	7:30 PM	9:30	8:45	0:45
7	GHIK	112		10:00 AM	7:30 PM	9:30	8:45	0:45											10:00 AM	7:30 PM	9:30	8:45	0:45
8	Harneet Kaur	369		10:00 AM	7:30 PM	9:30	8:45	0:45											10:00 AM	7:30 PM	9:30	8:45	0:45
9	LKK	250		10:00 AM	7:30 PM	9:30	8:45	0:45											10:00 AM	7:30 PM	9:30	8:45	0:45
10	TKK	589		10:00 AM	7:30 PM	9:30	8:45	0:45											10:00 AM	7:30 PM	9:30	8:45	0:45
11	KK	28		10:00 AM	7:30 PM	9:30	8:45	0:45											10:00 AM	7:30 PM	9:30	8:45	0:45
12	LL	33		10:00 AM	7:30 PM	9:30	8:45	0:45											10:00 AM	7:30 PM	9:30	8:45	0:45
13	MM	366		10:00 AM	7:30 PM	9:30	8:45	0:45											10:00 AM	7:30 PM	9:30	8:45	0:45

 

[FormR]

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