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
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies