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
Highlighted

@Peter_Kelley 

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

image.png

 

Highlighted

@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.

Highlighted

@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