Home

Highlighting cells next to those that are conditionally formatted

%3CLINGO-SUB%20id%3D%22lingo-sub-912975%22%20slang%3D%22en-US%22%3EHighlighting%20cells%20next%20to%20those%20that%20are%20conditionally%20formatted%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-912975%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20find%20help%20with%20a%20fire%20department%20shift%20calendar.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CUL%3E%3CLI%3EA-Shift%3A%20Red%3C%2FLI%3E%3CLI%3EB-Shift%3A%20Green%3C%2FLI%3E%3CLI%3EC-Shift%3A%20Blue%3C%2FLI%3E%3CLI%3ED-Shift%3A%20Yellow%3C%2FLI%3E%3C%2FUL%3E%3CP%3EEach%20employee%20is%20assigned%20to%20a%20shift%2C%20along%20with%20a%20number%20(e.g.%20A1%2C%20B6%2C%20C4%2C%20D8).%20This%20called%20a%20debit%20day%2C%20which%20is%20an%20extra%20day%20that%20an%20employee%20works%20in%20a%2032-day%20cycle%20to%20bring%20their%20work%20hours%20up%20to%2048-hours%20per%20week.%20We%20have%20the%20formula%20that%20creates%20the%20debit%20day%20based%20on%20January%201st%20of%20each%20year.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThen%20we%20add%20conditional%20formatting%20to%20highlight%20the%20debit%20shifts%20to%20their%20respective%20colors%20(bulleted%20list%20above).%20Currently%2C%20I%20manually%20highlight%20the%20date%20cells%20in%20the%20%222020%20Calendar%22%20worksheet%2C%20so%20the%20end%20product%20looks%20like%20the%20%22After%20manual%20HL%22%20worksheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENow%2C%20for%20my%20question.%20Is%20there%20a%20conditional%20formatting%20rule%20that%20will%20automatically%20highlight%20the%20date%20cell%20(number%20form)%20the%20same%20color%20as%20the%20debit%20day%20cell%20to%20the%20right%20of%20the%20date%3F%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-912975%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-914496%22%20slang%3D%22en-US%22%3ERe%3A%20Highlighting%20cells%20next%20to%20those%20that%20are%20conditionally%20formatted%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-914496%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425821%22%20target%3D%22_blank%22%3E%40Fyrmn472%3C%2FA%3E%26nbsp%3B%26nbsp%3B%20I've%20not%20got%20a%20full%20solution%20yet%2C%20but%20I'm%20pretty%20sure%20it%20can%20be%20done.%20Just%20looking%20at%20what%20you%20have%2C%20though%2C%20it%20seems%20to%20me%20that%20you%20are%20doing%20things%20the%20hard%20way%20(this%20just%20in%20connection%20with%20your%20conditional%20formatting%20rules).%20You%20have%20created%20a%20gargantuan%20set%20of%20conditions%2C%20essentially%20one%20rule%20for%20each%20person%20(assuming%20that%20A1-D9%20refer%20to%20different%20individuals).%20You%20could%20be%20a%20lot%20more%20efficient%20by%20just%20looking%20at%20the%20first%20letter%20of%20each%20box%2C%20using%20something%20like%20this%20in%20the%20condition%2C%20%3DIF(LEFT(A1%2C1)%3D%22A%22)%20%2C%20a%20formula%20that%20gets%20the%20left%20most%20character%20and%20compares%20it%20with%20%22A%22%20in%20this%20case%2C%20and%20then%20that%20can%20produce%20the%20color%20for%20%3CSTRONG%3EALL%3C%2FSTRONG%3E%20the%20members%20of%20the%20A%20shift.%20And%20so%20on%20down%20the%20line.%20In%20short%2C%20you'd%20end%20up%20with%20four%20lines%20in%20the%20conditional%20formatting%20rule.%20Rather%20than%20umpty-ump.%3C%2FP%3E%3CP%3EYou%20might%20have%20to%20play%20around%20a%20bit%20to%20get%20the%20syntax%20right%20for%20the%20IF%20formula%20there%20in%20the%20conditional%20formatting%3B%20those%20are%20always%20tricky.%3C%2FP%3E%3CP%3EThat%20done%2C%20I%20know%20you%20can%20refer%20to%20other%20columns%20in%20conditional%20formatting%20rules%2C%20so%20they%20apply%20a%20color%20to%20Column%20A%20when%20certain%20conditions%20are%20met%20in%20Column%20B%3B%20adjacent%20cells%20in%20a%20calendar%20format%20are%20a%20lot%20trickier%20and%20I'll%20not%20be%20able%20to%20spend%20any%20more%20time%20(Sorry)..%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20I%20hope%20that%20other%20idea%20can%20be%20helpful%2C%20if%20not%20this%20year%2C%20perhaps%20in%20the%20future.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Fyrmn472
Occasional Visitor

Hello,

 

I am trying to find help with a fire department shift calendar. 

 

  • A-Shift: Red
  • B-Shift: Green
  • C-Shift: Blue
  • D-Shift: Yellow

Each employee is assigned to a shift, along with a number (e.g. A1, B6, C4, D8). This called a debit day, which is an extra day that an employee works in a 32-day cycle to bring their work hours up to 48-hours per week. We have the formula that creates the debit day based on January 1st of each year.

 

Then we add conditional formatting to highlight the debit shifts to their respective colors (bulleted list above). Currently, I manually highlight the date cells in the "2020 Calendar" worksheet, so the end product looks like the "After manual HL" worksheet.

 

Now, for my question. Is there a conditional formatting rule that will automatically highlight the date cell (number form) the same color as the debit day cell to the right of the date?  

1 Reply

@Fyrmn472   I've not got a full solution yet, but I'm pretty sure it can be done. Just looking at what you have, though, it seems to me that you are doing things the hard way (this just in connection with your conditional formatting rules). You have created a gargantuan set of conditions, essentially one rule for each person (assuming that A1-D9 refer to different individuals). You could be a lot more efficient by just looking at the first letter of each box, using something like this in the condition, =IF(LEFT(A1,1)="A") , a formula that gets the left most character and compares it with "A" in this case, and then that can produce the color for ALL the members of the A shift. And so on down the line. In short, you'd end up with four lines in the conditional formatting rule. Rather than umpty-ump.

You might have to play around a bit to get the syntax right for the IF formula there in the conditional formatting; those are always tricky.

That done, I know you can refer to other columns in conditional formatting rules, so they apply a color to Column A when certain conditions are met in Column B; adjacent cells in a calendar format are a lot trickier and I'll not be able to spend any more time (Sorry)..

 

But I hope that other idea can be helpful, if not this year, perhaps in the future.

 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
50 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
32 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
15 Replies
Dev channel update to 80.0.355.1 is live
josh_bodner in Discussions on
67 Replies