SOLVED
Home

Excel Conditional Formatting / Modification to "Stop If True"

%3CLINGO-SUB%20id%3D%22lingo-sub-706287%22%20slang%3D%22en-US%22%3EExcel%20Conditional%20Formatting%20%2F%20Modification%20to%20%22Stop%20If%20True%22%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-706287%22%20slang%3D%22en-US%22%3E%3CP%3EGood%20morning.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3ETL%3BDR%3C%2FSTRONG%3E%3A%20I%20am%20trying%20to%20get%20a%20dynamic%20border%20that%20only%20applies%20to%20my%20very%20last%20row.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3ETHE%20REST%3C%2FSTRONG%3E%3A%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20an%20Excel%20spreadsheet%20of%20my%20active%20cases%20I'm%20working%20on.%20I%20have%20several%20different%20conditional%20formatting%20rules%20running%2C%20but%20one%20thing%20I%20would%20like%20to%20do%20is%20add%20a%20border%20that%26nbsp%3B%3CEM%3Eonly%3C%2FEM%3E%20activates%20for%20the%20last%20row.%20Essentially%2C%20a%20dynamic%20border%20that%20always%20underlines%20the%20lowest%20row.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20tried%20a%20number%20of%20solutions%20that%20aren't%20working.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%5B1%5D%20I%20started%20out%20by%20trying%20%3D%24A2%26lt%3B%26gt%3B%22%22%20(applied%20to%20%241%3A%241048576).%3C%2FP%3E%3CP%3E--%20Column%20A%20is%20Case%20%23%2C%20so%20this%20checks%20the%20row%20below%20my%20target%20row%20to%20see%20if%20I%20have%20entered%20a%20case%20number.%20If%20I%20have%2C%20then%20create%20a%20bottom%20border.%20This%20ended%20up%20doing%20the%20opposite%20of%20what%20I%20wanted%3A%20underlined%20every%20row%26nbsp%3B%3CEM%3Eexcept%3C%2FEM%3E%20the%20bottom%20row.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%5B2%5D%20I%20tried%20%3D%24A1%26lt%3B%26gt%3B%22%22%20(applied%20to%20%241%3A%241048576).%3C%2FP%3E%3CP%3E--%20Checks%20to%20see%20if%20%3CEM%3Ecurrent%3C%2FEM%3E%26nbsp%3Brow%20has%20a%20case%20number%20and%20applies%20a%20bottom%20border%20if%20so.%20This%20included%20my%20last%20row%2C%20but%20also%20included%20every%20other%20row.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%5B3%5D%20Switched%20to%20%3DISBLANK(A2)%20(applied%20to%20%241%3A%241048576).%3C%2FP%3E%3CP%3E--%20Checks%20to%20see%20if%20the%20row%20below%20my%20target%20row%20is%20blank.%20If%20it%20is%2C%20it%20applies%20the%20bottom%20border.%3C%2FP%3E%3CP%3E---%20This%26nbsp%3B%3CEM%3Ealmost%3C%2FEM%3E%20works%2C%20but%20it%20looks%20awful%20because%20it%20applies%20a%20border%20to%26nbsp%3B%3CEM%3Eevery%3C%2FEM%3E%20empty%20row%20beyond%20that.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhich%20leads%20to%20my%20question%3A%20%3CSTRONG%3Eis%20there%20a%20way%20to%20modify%20a%20conditional%20formatting%20rule%20so%20that%20it%20stops%20after%20it%20has%20been%20true%20once%3F%3C%2FSTRONG%3E%20Stop%20If%20True%20typically%20stops%20running%20further%20conditional%20formatting%20rules%2C%20not%20the%20rule%20itself.%20I%20need%20to%20modify%20my%20rule%20so%20that%20once%20it%20finds%20a%20single%20empty%20row%2C%20it%20applies%20the%20border%20once%20and%20then%20stops%20running.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-706287%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-706509%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Conditional%20Formatting%20%2F%20Modification%20to%20%22Stop%20If%20True%22%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-706509%22%20slang%3D%22en-US%22%3ETry%20this%3A%3CBR%20%2F%3E%3DROW()%3DCOUNTA(%24A%3A%24A)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-707122%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Conditional%20Formatting%20%2F%20Modification%20to%20%22Stop%20If%20True%22%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-707122%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F362848%22%20target%3D%22_blank%22%3E%40majorthompson%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20variant%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20464px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F119720i0205E9FF340495C3%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-707308%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Conditional%20Formatting%20%2F%20Modification%20to%20%22Stop%20If%20True%22%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-707308%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20both!%20Both%20formulas%20worked%20perfectly.%20Wish%20I%20could%20mark%20both%20as%20correct.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-707586%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Conditional%20Formatting%20%2F%20Modification%20to%20%22Stop%20If%20True%22%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-707586%22%20slang%3D%22en-US%22%3EOf%20course%2C%20there%20are%20several%20ways%20to%20achieve%20the%20same%20result.%20Knowing%20the%20expertise%20of%20%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%2C%20he%20will%20always%20find%20another%20way.%20It%20so%20happens%20that%20my%20formula%20is%20shorter%2C%20and%20thus%20inevitably%20faster%2C%20than%20his.%20The%20shorter%20and%20faster%20formula%20should%20be%20preferred%20at%20all%20times%2C%20so%20you%20don%E2%80%99t%20have%20to%20regret%20your%20choice.%20You%20made%20the%20right%20choice!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-707667%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Conditional%20Formatting%20%2F%20Modification%20to%20%22Stop%20If%20True%22%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-707667%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%2C%20I%20have%20no%20objections%20about%20the%20choice%2C%20just%20a%20comment%20on%20what%20faster%20formula%20is%20always%20has%20high%20priority.%20Maintainability%20and%20flexibility%20are%20often%20more%20important%26nbsp%3B%20than%20saving%20of%20few%20milliseconds%20in%20performance.%20As%20an%20example%20is%20shifting%20on%20such%20scenario%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%2094px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F119752iBA65B3A7A949F851%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-709496%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Conditional%20Formatting%20%2F%20Modification%20to%20%22Stop%20If%20True%22%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-709496%22%20slang%3D%22en-US%22%3EI%20agree%20%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.%3C%2FLINGO-BODY%3E
majorthompson
New Contributor

Good morning.

 

TL;DR: I am trying to get a dynamic border that only applies to my very last row.

 

THE REST

I have an Excel spreadsheet of my active cases I'm working on. I have several different conditional formatting rules running, but one thing I would like to do is add a border that only activates for the last row. Essentially, a dynamic border that always underlines the lowest row.

 

I've tried a number of solutions that aren't working.

 

[1] I started out by trying =$A2<>"" (applied to $1:$1048576).

-- Column A is Case #, so this checks the row below my target row to see if I have entered a case number. If I have, then create a bottom border. This ended up doing the opposite of what I wanted: underlined every row except the bottom row.

 

[2] I tried =$A1<>"" (applied to $1:$1048576).

-- Checks to see if current row has a case number and applies a bottom border if so. This included my last row, but also included every other row.

 

[3] Switched to =ISBLANK(A2) (applied to $1:$1048576).

-- Checks to see if the row below my target row is blank. If it is, it applies the bottom border.

--- This almost works, but it looks awful because it applies a border to every empty row beyond that.

 

Which leads to my question: is there a way to modify a conditional formatting rule so that it stops after it has been true once? Stop If True typically stops running further conditional formatting rules, not the rule itself. I need to modify my rule so that once it finds a single empty row, it applies the border once and then stops running.

6 Replies
Solution
Try this:
=ROW()=COUNTA($A:$A)

Thank you both! Both formulas worked perfectly. Wish I could mark both as correct.

Of course, there are several ways to achieve the same result. Knowing the expertise of @Sergei Baklan, he will always find another way. It so happens that my formula is shorter, and thus inevitably faster, than his. The shorter and faster formula should be preferred at all times, so you don’t have to regret your choice. You made the right choice!

@Twifoo , I have no objections about the choice, just a comment on what faster formula is always has high priority. Maintainability and flexibility are often more important  than saving of few milliseconds in performance. As an example is shifting on such scenario

image.png

 

Related Conversations