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