Conditional Formatting for Schedule

%3CLINGO-SUB%20id%3D%22lingo-sub-1548046%22%20slang%3D%22en-US%22%3EConditional%20Formatting%20for%20Schedule%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1548046%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%20I%20was%20wondering%20if%20someone%20could%20help%20me%20with%20a%20query%20for%20my%20excel%20spreadsheet%20I%20use%20for%20school.%20I%20am%20switching%20from%20Google%20Sheets%20to%20Excel%20and%20I%20had%20a%20conditional%20formatting%20formula%20set%20up%20to%20recognize%20the%20day%20of%20week%20by%20number%20and%20time%2C%20in%20order%20to%20highlight%20a%20group%20of%20cells%20containing%20text%20describing%20class%20details.%20So%2C%20in%20basic%20terms%2C%20I%20want%20it%20to%20highlight%20which%20class%20I%20am%20in.%26nbsp%3B%20%26nbsp%3B%20The%20merged%20cell%20columns%20can%20be%20highlighted%20all%20at%20once%2C%20I%20realize.%20There%20are%20two%20different%20schedules%2C%20so%20if%20you%20could%20figure%20out%20both%20that%20would%20be%20awesome!%20I%20would%20love%20it%20if%20you%20could%20help.%20If%20you%20have%20any%20questions%2C%20let%20me%20know.%20**Pardon%20the%20terrible%20formatting%2C%20to%20be%20fixed%20later....%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1548046%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20for%20web%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1548231%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20for%20Schedule%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1548231%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F741155%22%20target%3D%22_blank%22%3E%40nlangbo%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESee%20the%20attached%20spreadsheet.%20If%20you%20want%20to%20change%20anything%2C%20like%20the%20color%2C%20select%20the%20cell%20%26gt%3B%20Conditional%20formatting%20%26gt%3B%20Manage%20Rules.%20You%20will%20see%20the%20formula%20I%20used%20for%20each%20section%20of%20your%20schedule%20and%20the%20background%20color.%26nbsp%3B%20%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Bennadeau_0-1595865830202.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F208209i75C288FE1B612778%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Bennadeau_0-1595865830202.png%22%20alt%3D%22Bennadeau_0-1595865830202.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1548263%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20for%20Schedule%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1548263%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3BThat%20is%20awesome!%20Are%20you%20able%20to%20do%20the%20second%20sheet%20as%20well!%20Still%20learning...%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F703724%22%20target%3D%22_blank%22%3E%40Bennadeau%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1548272%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20for%20Schedule%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1548272%22%20slang%3D%22en-US%22%3E%3CP%3EAlso%2C%20I%20am%20not%20sure%20where%20to%20edit%20the%20color%20scheme%20of%20the%20formulas%3F%20I%20see%20how%20to%20manage%20the%20formatting%20but%20not%20how%20to%20edit%20it.%20If%20I%20had%20separate%20groups%20of%20cells%20where%20it%20is%20currently%20merged%2C%20would%20i%20just%20use%20the%20same%20formula%3F%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F703724%22%20target%3D%22_blank%22%3E%40Bennadeau%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1548445%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20for%20Schedule%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1548445%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F741155%22%20target%3D%22_blank%22%3E%40nlangbo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETo%20change%20the%20color%2C%20select%20the%20entire%20schedule%20and%20follow%20the%20arrows%20in%20the%20screenshot%20attached.%20You%20will%20have%20to%20do%20this%20for%20every%20single%20conditional%20formatting%20one%20at%20the%20time.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Bennadeau_0-1595873028716.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F208226iBDE75F4216E1ACC1%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Bennadeau_0-1595873028716.png%22%20alt%3D%22Bennadeau_0-1595873028716.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EAttached%20is%20the%20workbook%20with%20the%20second%20sheet%20completed.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1548474%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20for%20Schedule%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1548474%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F741155%22%20target%3D%22_blank%22%3E%40nlangbo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20try%20this%20approach%20instead%20of%20writing%20for%20each%20scenario%2C%20you%20can%20actually%20write%20for%20the%20same%20period%20in%20each%20day.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20results%20in%20fewer%20number%20of%20formula.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESee%20attached%20the%20workbook.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECheers%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hello, I was wondering if someone could help me with a query for my excel spreadsheet I use for school. I am switching from Google Sheets to Excel and I had a conditional formatting formula set up to recognize the day of week by number and time, in order to highlight a group of cells containing text describing class details. So, in basic terms, I want it to highlight which class I am in.    The merged cell columns can be highlighted all at once, I realize. There are two different schedules, so if you could figure out both that would be awesome! I would love it if you could help. If you have any questions, let me know. **Pardon the terrible formatting, to be fixed later....

5 Replies

Hi @nlangbo,

 

See the attached spreadsheet. If you want to change anything, like the color, select the cell > Conditional formatting > Manage Rules. You will see the formula I used for each section of your schedule and the background color.   

 

Bennadeau_0-1595865830202.png

 

 That is awesome! Are you able to do the second sheet as well! Still learning...@Bennadeau 

Also, I am not sure where to edit the color scheme of the formulas? I see how to manage the formatting but not how to edit it. If I had separate groups of cells where it is currently merged, would i just use the same formula? @Bennadeau 

@nlangbo 

 

To change the color, select the entire schedule and follow the arrows in the screenshot attached. You will have to do this for every single conditional formatting one at the time.

Bennadeau_0-1595873028716.png

Attached is the workbook with the second sheet completed.

 

 

Hi @nlangbo 

 

You can try this approach instead of writing for each scenario, you can actually write for the same period in each day.

 

This results in fewer number of formula.

 

See attached the workbook.

 

Cheers