SOLVED
Home

conditional formatting

%3CLINGO-SUB%20id%3D%22lingo-sub-339006%22%20slang%3D%22en-US%22%3Econditional%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-339006%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20I%20am%20using%20a%20number%20of%20rules%20in%20excel%20here%20is%20my%20example%2C%20box%20M5%20has%20a%20date%20written%20in%20it%2C%20Box%20N5%20has%20a%20formula%20to%20generate%20a%20date%20in%207%20days%20time%20to%20remind%20me%20to%20send%20an%20email.%20N5%20has%20conditional%20formatting%20to%20be%20green%20amber%2C%20red.%20I%20have%20copied%20and%20paste%20this%20down%20both%20columns%20so%20rules%20and%20formatting%20apply.%20I%20have%20column%20P%20which%20is%20formatted%20to%20a%20yes%20no%20drop%20down.%20I%20have%20managed%20to%20make%20N5%20change%20to%20purple%20when%20P5%20changes%20to%20yes.%20If%20I%20change%20the%20drop%20down%20box%20in%20P5%20to%20no%20N5%20stays%20the%20date%20color.%20When%20I%20try%20and%20paste%20that%20rule%20down%20all%20the%20boxes%20in%20column%20N%20go%20off%20box%20P5%20instead%20of%20the%20box%20opposite%20it%20in%20Column%20P.%20I.e%20N8%20will%20be%20purple%20even%20though%20P8%20says%20no.%20If%20I%20change%20P5%20to%20no%20all%20boxes%20in%20column%20N%20change%20back%20to%20date%20colors.%20Is%20there%20a%20way%20to%20copy%20and%20paste%20this%20rule%20so%20I%20dont%20have%20to%20individually%20do%20it%20by%20box.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-339006%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-340022%22%20slang%3D%22en-US%22%3ERe%3A%20conditional%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-340022%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Ed%2C%20you%20may%20not%20have%20understood%20but%20you%20totally%20gave%20me%20the%20answer%20I%20think%20because%20I%20was%20putting%20the%20dollar%20sign%20before%20the%20number%20was%20limiting%20it%20to%20one%20cell%2C%20so%20removed%20it%20and%20I%20can%20now%20copy%20and%20paste%20away.%20Thank%20you!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-339470%22%20slang%3D%22en-US%22%3ERe%3A%20conditional%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-339470%22%20slang%3D%22en-US%22%3EI'm%20not%20sure%20I%20followed%20everything%20you%20were%20talking%20about%20there%2C%20but%20you%20can%20set%20conditional%20rules%20to%20be%20ranges%2C%20not%20just%20cells.%20So%20if%20you%20apply%20a%20rule%20and%20set%20it%20to%20A1%3AA10%2C%20all%2010%20cells%20will%20evaluate%20that%20rule%20and%20apply%20it%20on%20a%20cell%20by%20cell%20basis.%20When%20doing%20this%2C%20you%20need%20to%20pay%20attention%20to%20the%20formula%20you%20are%20testing%20for%20relative%20or%20absolute%20references.%20Usually%20you%20want%20relative%2C%20but%20sometimes%20absolute%20(%24B%241)%20or%20partially%20relative.%20(%24B1%3A%24B10)%20or%20(B%241%3AB%2410)%3C%2FLINGO-BODY%3E
TracyB
New Contributor

Hi I am using a number of rules in excel here is my example, box M5 has a date written in it, Box N5 has a formula to generate a date in 7 days time to remind me to send an email. N5 has conditional formatting to be green amber, red. I have copied and paste this down both columns so rules and formatting apply. I have column P which is formatted to a yes no drop down. I have managed to make N5 change to purple when P5 changes to yes. If I change the drop down box in P5 to no N5 stays the date color. When I try and paste that rule down all the boxes in column N go off box P5 instead of the box opposite it in Column P. I.e N8 will be purple even though P8 says no. If I change P5 to no all boxes in column N change back to date colors. Is there a way to copy and paste this rule so I dont have to individually do it by box.

2 Replies
Solution
I'm not sure I followed everything you were talking about there, but you can set conditional rules to be ranges, not just cells. So if you apply a rule and set it to A1:A10, all 10 cells will evaluate that rule and apply it on a cell by cell basis. When doing this, you need to pay attention to the formula you are testing for relative or absolute references. Usually you want relative, but sometimes absolute ($B$1) or partially relative. ($B1:$B10) or (B$1:B$10)

Hi Ed, you may not have understood but you totally gave me the answer I think because I was putting the dollar sign before the number was limiting it to one cell, so removed it and I can now copy and paste away. Thank you!

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies