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
Highlighted
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
Highlighted
Solution
Try this:
=ROW()=COUNTA($A:$A)
Highlighted
Highlighted

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

Highlighted
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!
Highlighted

@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

 

Highlighted
Related Conversations
Help with counting formula
AL789 in Excel on
1 Replies
Excel hangs up during execution of any operation
Rohit_Agrawal in Excel on
0 Replies
how to create custom number format permanently
iman89 in Excel on
0 Replies
drop-down list vs if-then vs ??
thetis in Excel on
0 Replies
RTD
adriangray in Excel on
0 Replies
Convert Days to whole months
SPC123 in Excel on
2 Replies