conditional formatting formula based on what day of the week

jbrown021286

Board Regular
Joined
Mar 13, 2023
Messages
81
Office Version
  1. 365
Platform
  1. Windows
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.
Screenshot 2023-07-19 212122.png
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
You'll have to create the rules on your own. But here they are:

Book1
FGHIJKLMNOPQRSTUVWXYZ
1UOHKHNPELVGSDIPELN``JDYBNDTesting DayFri 2023-07-28
2UVNUEVIAAZYUKQEEHHDKLYKRCNORULErule formula
3QYPXBWPGKZVRFJQRIIGDMLEMACEMonJ VFALSE=WEEKDAY($X$1,2)=1
4IRQAOVSCNLKINXAGKQRFLMCBWAFTuesNFALSE=WEEKDAY($X$1,2)=2
5AQYXZYOHPCKSIKKZGAQSTXOBYDYWedP LFALSE=WEEKDAY($X$1,2)=3
6UOSEHOCYYIFTEDIAQNHLXXBVAKUEven FriF RTRUE=AND(WEEKDAY($X$1,2)=5,ISEVEN(NETWORKDAYS.INTL(DATE(2023,1,1),$X$1,"1111011")))
7NSAEXNYCPSQTQKPFJTBOKFBINYJOdd FriH RFALSE=AND(WEEKDAY($X$1,2)=5,ISODD(NETWORKDAYS.INTL(DATE(2023,1,1),$X$1,"1111011")))
8YPVZLBVBNNTUYJCKZWOPVDXDBFZ
9ARGHTOPFKQKLPVUJCUDODNFRLOL
10OJDFWHVBCDUGGZAZMGLTCIDNRWC
Sheet1
Cell Formulas
RangeFormula
Y3Y3=WEEKDAY($X$1,2)=1
Z3:Z7Z3=FORMULATEXT(Y3)
Y4Y4=WEEKDAY($X$1,2)=2
Y5Y5=WEEKDAY($X$1,2)=3
Y6Y6=AND(WEEKDAY($X$1,2)=5,ISEVEN(NETWORKDAYS.INTL(DATE(2023,1,1),$X$1,"1111011")))
Y7Y7=AND(WEEKDAY($X$1,2)=5,ISODD(NETWORKDAYS.INTL(DATE(2023,1,1),$X$1,"1111011")))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
H1:H10,R1:R10Expression=AND(WEEKDAY($X$1,2)=5,ISODD(NETWORKDAYS.INTL(DATE(2023,1,1),$X$1,"1111011")))textNO
F1:F10,R1:R10Expression=AND(WEEKDAY($X$1,2)=5,ISEVEN(NETWORKDAYS.INTL(DATE(2023,1,1),$X$1,"1111011")))textNO
P1:P10,L1:L10Expression=WEEKDAY($X$1,2)=3textNO
N1:N10Expression=WEEKDAY($X$1,2)=2textNO
J1:J10,V1:V10Expression=WEEKDAY($X$1,2)=1textNO
 
Upvote 0
Solution
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.
 
Upvote 0
Well, I was wrong, the Row 7 and Row 10 CF formulas go in the bottom:

Book1
EFGHIJKLMNOPQRSTUVWXYZ
1UOHKHNPELVGSDIPELN``JDYBNDTesting DayFri 2023-07-28
2UVNUEVIAAZYUKQEEHHDKLYKRCNORULErule formula
3QYPXBWPGKZVRFJQRIIGDMLEMACEMonJ VFALSE=WEEKDAY($X$1,2)=1
4IRQAOVSCNLKINXAGKQRFLMCBWAFTuesNFALSE=WEEKDAY($X$1,2)=2
5AQYXZYOHPCKSIKKZGAQSTXOBYDYWedP LFALSE=WEEKDAY($X$1,2)=3
6UOSEHOCYYIFTEDIAQNHLXXBVAKUEven FriF RTRUE=AND(WEEKDAY($X$1,2)=5,ISEVEN(NETWORKDAYS.INTL(DATE(2023,1,1),$X$1,"1111011")))
7TRUENSAEXNYCPSQTQKPFJTBOKFBINYJOdd FriH RFALSE=AND(WEEKDAY($X$1,2)=5,ISODD(NETWORKDAYS.INTL(DATE(2023,1,1),$X$1,"1111011")))
8YPVZLBVBNNTUYJCKZWOPVDXDBFZRow 7 TRUETRUE=$E7=TRUE
9ARGHTOPFKQKLPVUJCUDODNFRLOLROW 10 TRUETRUE=$E10=TRUE
10TRUEOJDFWHVBCDUGGZAZMGLTCIDNRWC
Sheet1
Cell Formulas
RangeFormula
Y3Y3=WEEKDAY($X$1,2)=1
Z3:Z9Z3=FORMULATEXT(Y3)
Y4Y4=WEEKDAY($X$1,2)=2
Y5Y5=WEEKDAY($X$1,2)=3
Y6Y6=AND(WEEKDAY($X$1,2)=5,ISEVEN(NETWORKDAYS.INTL(DATE(2023,1,1),$X$1,"1111011")))
Y7Y7=AND(WEEKDAY($X$1,2)=5,ISODD(NETWORKDAYS.INTL(DATE(2023,1,1),$X$1,"1111011")))
Y8Y8=$E7=TRUE
Y9Y9=$E10=TRUE
Cells with Conditional Formatting
CellConditionCell FormatStop If True
H1:H10,R1:R10Expression=AND(WEEKDAY($X$1,2)=5,ISODD(NETWORKDAYS.INTL(DATE(2023,1,1),$X$1,"1111011")))textNO
F1:F10,R1:R10Expression=AND(WEEKDAY($X$1,2)=5,ISEVEN(NETWORKDAYS.INTL(DATE(2023,1,1),$X$1,"1111011")))textNO
P1:P10,L1:L10Expression=WEEKDAY($X$1,2)=3textNO
N1:N10Expression=WEEKDAY($X$1,2)=2textNO
J1:J10,V1:V10Expression=WEEKDAY($X$1,2)=1textNO
E7:V7Expression=$E7=TRUEtextNO
E10:V10Expression=$E10=TRUEtextNO





Here is the manage rules dialog box:

1689822003501.png
 
Upvote 0

Forum statistics

Threads
1,223,909
Messages
6,175,315
Members
452,634
Latest member
cpostell

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top