Conditional formatting on a cell containing a formula

Teladianium

New Member
Joined
Apr 10, 2012
Messages
15
Hi all,

I am trying to build a calendar so I can change the year and get a new year. I have got the days of the month to work but I want to conditionally format the weekend days. I work where there are 2 weekends. Some operated Fri/Sat and the others Sat/Sun. so I need it to be dynamic. The snip below is showing my issue. I need the weekend to colour for the days of the month that are numbered and not for the days that are blank (Feb 29)

I am not too great with excel and relying on copy/pasting with modification to get stuff to work. so please bare with me.
1674777976295.png


The formula in the row for the dates is =IF(MONTH($A14)<>MONTH($A14-WEEKDAY($A14,1)+(COLUMN(AE14)-COLUMN($B14)+1)),"",$A14-WEEKDAY($A14,1)+(COLUMN(AE14)-COLUMN($B14)+1))
this is giving the desired effect allowing me to change the year and the months then reflect accordingly. so all the cells that need conditional formatting have formulas in them.

I have tried things like
ISFORMULA(AJ22>0)
ISFORMULA(AJ22=10) "Just to see if it returned something"

but to be honest I am just stabbing in the dark. Any help is really appreciated.
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
what distinguishes if Fri/Sat or Sat/Sun are the weekend? Also would be best if you use XL2BB to copy your data in the post
 
Upvote 0
Hi Hajiali,

Thanks for your reply,
I have a dropdown data validation for the weekend selection in cell R3. I have conditional formatting to manage the colours on the days row.

I had to google XL2BB 🙃. so trying to use it now. hope it works.


Book8
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAM
1Rotation TrackerYear:2022
2Schedual start:TodayQDate of FlightcTrg / Course
3Week end:Fri - Sate Expected daysoOther LLoan
4
5
6
7SuMoTuWeThFrSaSuMoTuWeThFrSaSuMoTuWeThFrSaSuMoTuWeThFrSaSuMoTuWeThFrSaSuMo
82022 Calendar
92022SuMoTuWeThFrSaSuMoTuWeThFrSaSuMoTuWeThFrSaSuMoTuWeThFrSaSuMoTuWeThFrSaSuMo
10Jan      12345678910111213141516171819202122232425262728293031Jan
11
12
13
14Feb  12345678910111213141516171819202122232425262728       Feb
15
16
17
18Mar  12345678910111213141516171819202122232425262728293031    Mar
19
20
21
Calendar
Cell Formulas
RangeFormula
B10:AL10,B18:AL18,B14:AL14B10=IF(MONTH($A10)<>MONTH($A10-WEEKDAY($A10,1)+(COLUMN(B10)-COLUMN($B10)+1)),"",$A10-WEEKDAY($A10,1)+(COLUMN(B10)-COLUMN($B10)+1))
A10A10=DATE($R$1,1,1)
A14A14=DATE($R$1,2,1)
A18A18=DATE($R$1,3,1)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
H10,H14,H18,H22,H26,H30,O10,O14,O18,O22,O26,O30,V10,V14,V18,V22,V26,V30,AC10,AC14,AC18,AC22,AC26,AC30,AJ10,AJ22,AJ18,AJ14,AJ26,AJ30,H35,H39,H43,H47,H51,H55,O35,O39,O43,O47,O51,O55,V35,V39,V43,V47,V51,V55,AC35,AC39,AC43,AC47,AC51,AC55,AJ35,AJ39,AJ43,AJ47Expression="Isformula($AJ22=30)"textNO
G9,N9,U9,AB9,AI9Expression=$R$3="Fri - Sat"textNO
B9,I9,P9,W9,AD9,AK9Expression=$R$3="Sat - Sun"textNO
B9,I9,P9,W9,AD9,AK9Expression=$R$3="Fri - Sat"textNO
G7,N7,U7,AB7,AI7Expression=$R$3="Fri - Sat"textNO
B7,I7,P7,W7,AD7,AK7Expression=$R$3="Sat - Sun"textNO
B7,I7,P7,W7,AD7,AK7Expression=$R$3="Fri - Sat"textNO
H11:AL11,D15:AE15,F44:AI44,H48:AL48,E71:AI71,C79:AG79,F83:AI83,H88:AL88,D92:AH92,G96:AJ96,B100:AF100,E104:AH104,G108:AK108,D19:AH19,G23:AJ23,B27:AF27,E31:AH31,G36:AK36,C40:AG40,D52:AG52,E67:AF67,H75:AK75,F56:AJ56,B63:AF63Cell Value="N"textNO
H11:AL11,D15:AE15,F44:AI44,H48:AL48,E71:AI71,C79:AG79,F83:AI83,H88:AL88,D92:AH92,G96:AJ96,B100:AF100,E104:AH104,G108:AK108,D19:AH19,G23:AJ23,B27:AF27,E31:AH31,G36:AK36,C40:AG40,D52:AG52,E67:AF67,H75:AK75,F56:AJ56,B63:AF63Cell Value="L"textNO
AC20:AH20Cell Value="C"textNO
AC20:AH20Cell Value="x"textNO
AD16:AE16Cell Value="C"textNO
AD16:AE16Cell Value="x"textNO
D20Cell Value="C"textNO
D20Cell Value="x"textNO
E20Cell Value="C"textNO
E20Cell Value="x"textNO
W20Cell Value="C"textNO
W20Cell Value="x"textNO
N20:V20Cell Value="C"textNO
N20:V20Cell Value="x"textNO
M20Cell Value="C"textNO
M20Cell Value="x"textNO
Y16:AC16Cell Value="C"textNO
Y16:AC16Cell Value="x"textNO
X16Cell Value="C"textNO
X16Cell Value="x"textNO
AJ12Cell Value="C"textNO
AJ12Cell Value="x"textNO
W12Cell Value="C"textNO
W12Cell Value="x"textNO
J12:V12Cell Value="C"textNO
J12:V12Cell Value="x"textNO
H12:I12Cell Value="C"textNO
H12:I12Cell Value="x"textNO
H11:AL11,D15:AE15,F44:AI44,H48:AL48,E71:AI71,C79:AG79,F83:AI83,H88:AL88,D92:AH92,G96:AJ96,B100:AF100,E104:AH104,G108:AK108,D19:AH19,G23:AJ23,B27:AF27,E31:AH31,G36:AK36,C40:AG40,D52:AG52,E67:AF67,H75:AK75,F56:AJ56,B63:AF63Cell Value="e"textNO
H11:AL11,D15:AE15,F44:AI44,H48:AL48,E71:AI71,C79:AG79,F83:AI83,H88:AL88,D92:AH92,G96:AJ96,B100:AF100,E104:AH104,G108:AK108,D19:AH19,G23:AJ23,B27:AF27,E31:AH31,G36:AK36,C40:AG40,D52:AG52,E67:AF67,H75:AK75,F56:AJ56,B63:AF63Cell Value="u"textNO
H11:AL11,D15:AE15,F44:AI44,H48:AL48,E71:AI71,C79:AG79,F83:AI83,H88:AL88,D92:AH92,G96:AJ96,B100:AF100,E104:AH104,G108:AK108,D19:AH19,G23:AJ23,B27:AF27,E31:AH31,G36:AK36,C40:AG40,D52:AG52,E67:AF67,H75:AK75,F56:AJ56,B63:AF63Cell Value="o"textNO
H11:AL11,D15:AE15,F44:AI44,H48:AL48,E71:AI71,C79:AG79,F83:AI83,H88:AL88,D92:AH92,G96:AJ96,B100:AF100,E104:AH104,G108:AK108,D19:AH19,G23:AJ23,B27:AF27,E31:AH31,G36:AK36,C40:AG40,D52:AG52,E67:AF67,H75:AK75,F56:AJ56,B63:AF63Cell Value="C"textNO
H11:AL11,D15:AE15,F44:AI44,H48:AL48,E71:AI71,C79:AG79,F83:AI83,H88:AL88,D92:AH92,G96:AJ96,B100:AF100,E104:AH104,G108:AK108,D19:AH19,G23:AJ23,B27:AF27,E31:AH31,G36:AK36,C40:AG40,D52:AG52,E67:AF67,H75:AK75,F56:AJ56,B63:AF63Cell Value="x"textNO
Cells with Data Validation
CellAllowCriteria
R2:T2ListStart of year, Today, select date
R3:T3ListFri - Sat, Sat - Sun
 
Upvote 0
Try This Formula in The Conditional Formatting for the days Keep in mind that the value in R3 has to match "FRI - SAT" or "SAT - SUN" with the space in between unless you change the formula below

Excel Formula:
=OR(AND($R$3="SAT - SUN",OR(TEXT(B10,"DDD")="SAT",TEXT(B10,"DDD")="SUN")),AND($R$3="FRI - SAT",OR(TEXT(B10,"DDD")="FRI",TEXT(B10,"DDD")="SAT")))
 
Upvote 0
Solution

Forum statistics

Threads
1,224,817
Messages
6,181,148
Members
453,021
Latest member
Justyna P

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