Home

Conditional LIne/Border insetion

%3CLINGO-SUB%20id%3D%22lingo-sub-868157%22%20slang%3D%22en-US%22%3EConditional%20LIne%2FBorder%20insetion%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-868157%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSTRONG%3ETask%3C%2FSTRONG%3E%3A%20to%20watch%20changes%20in%20column%20numbers%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EObjective%3A%3C%2FSTRONG%3E%3C%2FP%3E%3CUL%3E%3CLI%3EIf%20number%20increases%20from%20previous%20row%20then%20add%20border%20of%20one%20color%20(Green)%3C%2FLI%3E%3CLI%3EIf%20number%20decreases%20from%20previous%20then%20add%20border%20of%20another%20color%20(Red)%3C%2FLI%3E%3C%2FUL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EFormat%3C%2FSTRONG%3E%3A%20Through%20out%20one%20section%20of%20a%20spreadsheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EComments%3C%2FSTRONG%3E%3A%20I've%20looked%20at%20conditional%20formatting%20and%20it%20only%20'seems'%20to%20work%20on%20cell%20by%20cell%20conditioning.%26nbsp%3B%20Where%20I%20need%20several%20hundred%20cells%20considered%20(top%20to%20bottom)%20and%20marked.%26nbsp%3B%20Can%20this%20be%20done.%20and%20how%20is%20this%20done%20if%20it%20is%20possible%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-868157%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3Econditional%20formating%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-868272%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20LIne%2FBorder%20insetion%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-868272%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F368833%22%20target%3D%22_blank%22%3E%40Peter_Kelley%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20may%20apply%20two%20conditional%20formatting%20rule%20to%20your%20entire%20range%20starting%20from%20second%20cell%20in%20the%20range%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20731px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F132828iDEBB0CA8DC04800D%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-869278%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20LIne%2FBorder%20insetion%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-869278%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUsing%20a%20border%20as%20the%20changing%20element%2C%20rather%20than%20the%20background%2C%20I%20have%20done%20exactly%20what%20you%20show%2C%20and%20entire%20area%20effected%20only%20responds%20to%20evaluation%20of%20%3CU%3E%3CSTRONG%3ECell%20%24A%241%3C%2FSTRONG%3E%3C%2FU%3E.%26nbsp%3B%20The%20experience%20on%20my%20end%20suggests%20that%20I%20would%20have%20to%20apply%20the%20conditional%20formatting%20%3CSTRONG%3Emanually%3C%2FSTRONG%3E%20to%20each%20and%20every%20cell%20in%20the%20section%20that%20I%20want%20to%20look%20at.%20to%20achieve%20what%20I%20want.%3C%2FP%3E%3CP%3ETrying%20to%20be%20polite.%26nbsp%3B%20It%20would%20be%20faster%20for%20me%20just%20to%20do%20this%20manually%20(cell%20by%20cell)%20than%20attempting%20staging%20a%20conditional%20format%20that%20adds%20a%20border.%26nbsp%3B%20Sorta%20back%20to%20square%20one.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-871244%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20LIne%2FBorder%20insetion%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-871244%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F368833%22%20target%3D%22_blank%22%3E%40Peter_Kelley%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOf%20course%20that's%20your%20choice.%20As%20a%20comment%3C%2FP%3E%0A%3CP%3E-%20you%20shall%20use%20relative%20references%20in%20the%20formula%2C%20not%20absolute%20one%3C%2FP%3E%0A%3CP%3E-%20you%20may%20apply%20any%20format%20you%20wish%20-%20borders%2C%20background%2C%20fonts%2C%20whatever.%20It%20doesn'r%20depend%20on%20the%20rule%20formula%3C%2FP%3E%0A%3CP%3E-%20if%20you%20have%20few%20separate%20ranges%2C%20after%20applying%20CF%20to%20one%20of%20them%2C%20you%20may%20copy%20formatting%20on%20another%20one%20by%20Format%20Painter.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Peter_Kelley
New Contributor

Task: to watch changes in column numbers

 

Objective:

  • If number increases from previous row then add border of one color (Green)
  • If number decreases from previous then add border of another color (Red)

 

Format: Through out one section of a spreadsheet.

 

Comments: I've looked at conditional formatting and it only 'seems' to work on cell by cell conditioning.  Where I need several hundred cells considered (top to bottom) and marked.  Can this be done. and how is this done if it is possible?

3 Replies

@Peter_Kelley 

You may apply two conditional formatting rule to your entire range starting from second cell in the range

image.png

 

@Sergei Baklan 

Using a border as the changing element, rather than the background, I have done exactly what you show, and entire area effected only responds to evaluation of Cell $A$1.  The experience on my end suggests that I would have to apply the conditional formatting manually to each and every cell in the section that I want to look at. to achieve what I want.

Trying to be polite.  It would be faster for me just to do this manually (cell by cell) than attempting staging a conditional format that adds a border.  Sorta back to square one.

@Peter_Kelley 

Of course that's your choice. As a comment

- you shall use relative references in the formula, not absolute one

- you may apply any format you wish - borders, background, fonts, whatever. It doesn'r depend on the rule formula

- if you have few separate ranges, after applying CF to one of them, you may copy formatting on another one by Format Painter.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
21 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