conditional formatting formula based on what day of the week

[jbrown021286]

i need to highlight certain cells with conditional formatting based of what day of the week it currently is. I need J1:J10 and V1:V10 highlighted on Monday, N1:N10 on Tuesday P1:P10 and L1:L10 on Wednesday, F1:F10 H1:H10 and R1:R10 on Friday. also if it is possible with just F1:F10 and H1:H10 I would like them to alternate which one is highlighted every other week (example H highlighted on 7/21/2023 and F Highlighted on 7/28/2023) once again only if it is possible. i do currently have a conditional formatting formula that effects all of the row 7 and row 10 cells in these colums and want this to new formula to take priority if there are any special steps i need to take.

 

[awoohaw]

You'll have to create the rules on your own. But here they are:

Book1
	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1	UOH		KHN		PEL		VGS		DIP		ELN		``		JDY		BND	Testing Day	Fri 2023-07-28
2	UVN		UEV		IAA		ZYU		KQE		EHH		DKL		YKR		CNO			RULE	rule formula
3	QYP		XBW		PGK		ZVR		FJQ		RII		GDM		LEM		ACE	Mon	J V	FALSE	=WEEKDAY($X$1,2)=1
4	IRQ		AOV		SCN		LKI		NXA		GKQ		RFL		MCB		WAF	Tues	N	FALSE	=WEEKDAY($X$1,2)=2
5	AQY		XZY		OHP		CKS		IKK		ZGA		QST		XOB		YDY	Wed	P L	FALSE	=WEEKDAY($X$1,2)=3
6	UOS		EHO		CYY		IFT		EDI		AQN		HLX		XBV		AKU	Even Fri	F R	TRUE	=AND(WEEKDAY($X$1,2)=5,ISEVEN(NETWORKDAYS.INTL(DATE(2023,1,1),$X$1,"1111011")))
7	NSA		EXN		YCP		SQT		QKP		FJT		BOK		FBI		NYJ	Odd Fri	H R	FALSE	=AND(WEEKDAY($X$1,2)=5,ISODD(NETWORKDAYS.INTL(DATE(2023,1,1),$X$1,"1111011")))
8	YPV		ZLB		VBN		NTU		YJC		KZW		OPV		DXD		BFZ
9	ARG		HTO		PFK		QKL		PVU		JCU		DOD		NFR		LOL
10	OJD		FWH		VBC		DUG		GZA		ZMG		LTC		IDN		RWC
Sheet1

Cell Formulas
Range	Formula
Y3	Y3	=WEEKDAY($X$1,2)=1
Z3:Z7	Z3	=FORMULATEXT(Y3)
Y4	Y4	=WEEKDAY($X$1,2)=2
Y5	Y5	=WEEKDAY($X$1,2)=3
Y6	Y6	=AND(WEEKDAY($X$1,2)=5,ISEVEN(NETWORKDAYS.INTL(DATE(2023,1,1),$X$1,"1111011")))
Y7	Y7	=AND(WEEKDAY($X$1,2)=5,ISODD(NETWORKDAYS.INTL(DATE(2023,1,1),$X$1,"1111011")))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H1:H10,R1:R10	Expression	=AND(WEEKDAY($X$1,2)=5,ISODD(NETWORKDAYS.INTL(DATE(2023,1,1),$X$1,"1111011")))	text	NO
F1:F10,R1:R10	Expression	=AND(WEEKDAY($X$1,2)=5,ISEVEN(NETWORKDAYS.INTL(DATE(2023,1,1),$X$1,"1111011")))	text	NO
P1:P10,L1:L10	Expression	=WEEKDAY($X$1,2)=3	text	NO
N1:N10	Expression	=WEEKDAY($X$1,2)=2	text	NO
J1:J10,V1:V10	Expression	=WEEKDAY($X$1,2)=1	text	NO

 

[awoohaw]

to make these rules take priority over your row 7 and 10 rules I think you would need to put them ABOVE the rules list. But I am unsure if the other cells in the row would highlight or not. You'll have to experiment with it.

 

[awoohaw]

Well, I was wrong, the Row 7 and Row 10 CF formulas go in the bottom:

Book1
	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1		UOH		KHN		PEL		VGS		DIP		ELN		``		JDY		BND	Testing Day	Fri 2023-07-28
2		UVN		UEV		IAA		ZYU		KQE		EHH		DKL		YKR		CNO			RULE	rule formula
3		QYP		XBW		PGK		ZVR		FJQ		RII		GDM		LEM		ACE	Mon	J V	FALSE	=WEEKDAY($X$1,2)=1
4		IRQ		AOV		SCN		LKI		NXA		GKQ		RFL		MCB		WAF	Tues	N	FALSE	=WEEKDAY($X$1,2)=2
5		AQY		XZY		OHP		CKS		IKK		ZGA		QST		XOB		YDY	Wed	P L	FALSE	=WEEKDAY($X$1,2)=3
6		UOS		EHO		CYY		IFT		EDI		AQN		HLX		XBV		AKU	Even Fri	F R	TRUE	=AND(WEEKDAY($X$1,2)=5,ISEVEN(NETWORKDAYS.INTL(DATE(2023,1,1),$X$1,"1111011")))
7	TRUE	NSA		EXN		YCP		SQT		QKP		FJT		BOK		FBI		NYJ	Odd Fri	H R	FALSE	=AND(WEEKDAY($X$1,2)=5,ISODD(NETWORKDAYS.INTL(DATE(2023,1,1),$X$1,"1111011")))
8		YPV		ZLB		VBN		NTU		YJC		KZW		OPV		DXD		BFZ	Row 7 TRUE		TRUE	=$E7=TRUE
9		ARG		HTO		PFK		QKL		PVU		JCU		DOD		NFR		LOL	ROW 10 TRUE		TRUE	=$E10=TRUE
10	TRUE	OJD		FWH		VBC		DUG		GZA		ZMG		LTC		IDN		RWC
Sheet1

Cell Formulas
Range	Formula
Y3	Y3	=WEEKDAY($X$1,2)=1
Z3:Z9	Z3	=FORMULATEXT(Y3)
Y4	Y4	=WEEKDAY($X$1,2)=2
Y5	Y5	=WEEKDAY($X$1,2)=3
Y6	Y6	=AND(WEEKDAY($X$1,2)=5,ISEVEN(NETWORKDAYS.INTL(DATE(2023,1,1),$X$1,"1111011")))
Y7	Y7	=AND(WEEKDAY($X$1,2)=5,ISODD(NETWORKDAYS.INTL(DATE(2023,1,1),$X$1,"1111011")))
Y8	Y8	=$E7=TRUE
Y9	Y9	=$E10=TRUE

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H1:H10,R1:R10	Expression	=AND(WEEKDAY($X$1,2)=5,ISODD(NETWORKDAYS.INTL(DATE(2023,1,1),$X$1,"1111011")))	text	NO
F1:F10,R1:R10	Expression	=AND(WEEKDAY($X$1,2)=5,ISEVEN(NETWORKDAYS.INTL(DATE(2023,1,1),$X$1,"1111011")))	text	NO
P1:P10,L1:L10	Expression	=WEEKDAY($X$1,2)=3	text	NO
N1:N10	Expression	=WEEKDAY($X$1,2)=2	text	NO
J1:J10,V1:V10	Expression	=WEEKDAY($X$1,2)=1	text	NO
E7:V7	Expression	=$E7=TRUE	text	NO
E10:V10	Expression	=$E10=TRUE	text	NO

Here is the manage rules dialog box: