Color Rows Based on Empty Cells

%3CLINGO-SUB%20id%3D%22lingo-sub-1726881%22%20slang%3D%22en-US%22%3EColor%20Rows%20Based%20on%20Empty%20Cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1726881%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20list%20of%20clients%20going%20down%20Column%20A%2C%20with%20information%20about%20each%20client%20going%20across%20the%20rows%20to%20multiple%20columns.%26nbsp%3B%20When%20services%20are%20completed%20for%20a%20client%2C%20I%20fill%20in%20my%20%22Disposition%22%20column%20on%20that%20client's%20row.%26nbsp%3B%20If%20services%20are%20not%20complete%2C%20that%20client's%20%22Disposition%22%20cell%20block%20remains%20empty%20(or%20blank).%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'd%20like%20to%20have%20each%20client%20whose%20services%20are%20not%20complete%2C%20therefore%2C%20whose%20%22Disposition%22%20cell%20block%20is%20empty%2C%20to%20have%20their%20entire%20row%20highlighted%20Yellow.%26nbsp%3B%20Then%2C%20once%20I%20fill%20in%20that%20block%2C%20I%20want%20the%20Yellow%20highlight%20to%20go%20away.%26nbsp%3B%20That%20way%20I%20can%20always%20easily%20see%20which%20client%20in%20my%20long%20list's%20case%20is%20still%20pending.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20ideas%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1726881%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1727500%22%20slang%3D%22en-US%22%3ERe%3A%20Color%20Rows%20Based%20on%20Empty%20Cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1727500%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F813690%22%20target%3D%22_blank%22%3E%40jfowles%3C%2FA%3E%26nbsp%3BYou%20may%20use%20Conditional%20Formatting%20for%20this%2C%20as%20demonstrated%20in%20the%20picture%20below%20(taken%20on%20a%20Mac%2C%20but%20similar%20on%20for%20PC).%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202020-09-30%20at%2007.28.22.png%22%20style%3D%22width%3A%20756px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F222979i44920C2151CA5F78%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22Screenshot%202020-09-30%20at%2007.28.22.png%22%20alt%3D%22Screenshot%202020-09-30%20at%2007.28.22.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EAttached%20an%20example%20as%20well.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1769385%22%20slang%3D%22en-US%22%3ERe%3A%20Color%20Rows%20Based%20on%20Empty%20Cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1769385%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3BThank%20you%20so%20much!!!%20I've%20used%20your%20suggestion%20and%20it%20works!%26nbsp%3B%20But%20I%20want%20to%20tweak%20it%20a%20little%20and%20I'm%20not%20sure%20how.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20highlighted%20a%20massive%20section%20going%20all%20the%20way%20down%20to%20rows%20that%20don't%20have%20a%20client%20in%20them%20so%20I%20don't%20have%20to%20keep%20editing%20this%20rule%20every%20time%20I%20add%20a%20new%20client.%26nbsp%3B%20But%20now%20all%20the%20blank%20rows%20that%20don't%20have%20clients%20obviously%20don't%20have%20Dispositions.%26nbsp%3B%20So%2C%20all%20of%20those%20client-less%20rows%20are%20yellow.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'd%20like%20to%20do%20a%20formula%20to%20keep%20the%20current%20one%20in%20existence%2C%20but%20then%20to%20say%20if%20there's%20no%20client%20name%20in%20Column%20A%2C%20then%20there'd%20be%20no%20formatting%20regardless%20of%20the%20Disposition%20Column.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20tried%20and%20tried%20and%20can't%20seem%20to%20figure%20it%20out%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

I have a list of clients going down Column A, with information about each client going across the rows to multiple columns.  When services are completed for a client, I fill in my "Disposition" column on that client's row.  If services are not complete, that client's "Disposition" cell block remains empty (or blank).  

 

I'd like to have each client whose services are not complete, therefore, whose "Disposition" cell block is empty, to have their entire row highlighted Yellow.  Then, once I fill in that block, I want the Yellow highlight to go away.  That way I can always easily see which client in my long list's case is still pending.

 

Any ideas?

4 Replies
Highlighted

@jfowles You may use Conditional Formatting for this, as demonstrated in the picture below (taken on a Mac, but similar on for PC).

Screenshot 2020-09-30 at 07.28.22.png

Attached an example as well.

@Riny_van_Eekelen Thank you so much!!! I've used your suggestion and it works!  But I want to tweak it a little and I'm not sure how.

 

I highlighted a massive section going all the way down to rows that don't have a client in them so I don't have to keep editing this rule every time I add a new client.  But now all the blank rows that don't have clients obviously don't have Dispositions.  So, all of those client-less rows are yellow.

 

I'd like to do a formula to keep the current one in existence, but then to say if there's no client name in Column A, then there'd be no formatting regardless of the Disposition Column.

 

I've tried and tried and can't seem to figure it out

Highlighted

@jfowles You can change the Rule-formula to:

=AND(ISBLANK($F2),NOT(ISBLANK($A2)))

 

See attached.

Highlighted

@jfowles 

As a comment, another way is to place No Format rule on first position with Stop If True flag

image.png