Home

Conditional formula based on cell color

%3CLINGO-SUB%20id%3D%22lingo-sub-637166%22%20slang%3D%22en-US%22%3EConditional%20formula%20based%20on%20cell%20color%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-637166%22%20slang%3D%22en-US%22%3E%3CP%3EMy%20daughter%20is%20about%20to%20be%20deployed%20overseas%20and%20asked%20me%20for%20some%20help%20with%20her%20budget%20since%20she%20will%20be%20in%20a%20location%20without%20much%20in%20the%20way%20of%20tech.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20the%20base%20spreadsheet%20set%20up%20and%20working%20but%20I%20have%20what%20I%20hope%20is%20an%20easy%20question.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20can%20I%20set%20up%20a%20formula%20that%20will%20add%20the%20contents%20of%20A1%3AA15%20but%20remove%20a%20cell%20if%20its%20color%20changes%20to%20say%20green%3F%26nbsp%3B%20I%20want%20an%20easy%20way%20for%20her%20to%20color%20code%20what%20she%20has%20paid%20and%20have%20a%20running%20remainder.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-637166%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20mobile%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-637782%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formula%20based%20on%20cell%20color%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-637782%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F347313%22%20target%3D%22_blank%22%3E%40BSTREET%3C%2FA%3E%26nbsp%3B%2C%20that's%20only%20with%20VBA%20programming.%20Formulas%20work%20with%20cells%20values%2C%20not%20with%20their%20properties%20as%20color.%20Alternatively%20you%20may%20set%20some%20flag%20in%20B1%3AB15%20(e.g.%20%22x%22)%20for%20what%20was%20paid%20what%20not%2C%20and%20use%20SUMIF()%20to%20calculate%20total%20expenses.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20additional%20for%20better%20visibility%20you%20may%20apply%20conditional%20formatting%20to%20green%20paid%20items.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-637938%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formula%20based%20on%20cell%20color%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-637938%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20about%20this%20approach%3F%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20871px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F115819iE7F1DCF7176582BE%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Screenshot_1.png%22%20title%3D%22Screenshot_1.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20find%20it%20in%20the%20attached%20file.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F347313%22%20target%3D%22_blank%22%3E%40BSTREET%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-664476%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formula%20based%20on%20cell%20color%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-664476%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F35679%22%20target%3D%22_blank%22%3E%40Haytham%20Amairah%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20perfect!%26nbsp%3B%20Thank%20you%20very%20much.%3C%2FP%3E%3C%2FLINGO-BODY%3E
BSTREET
New Contributor

My daughter is about to be deployed overseas and asked me for some help with her budget since she will be in a location without much in the way of tech.

 

I have the base spreadsheet set up and working but I have what I hope is an easy question.

 

How can I set up a formula that will add the contents of A1:A15 but remove a cell if its color changes to say green?  I want an easy way for her to color code what she has paid and have a running remainder.

 

Thanks

3 Replies

@BSTREET , that's only with VBA programming. Formulas work with cells values, not with their properties as color. Alternatively you may set some flag in B1:B15 (e.g. "x") for what was paid what not, and use SUMIF() to calculate total expenses.

 

In additional for better visibility you may apply conditional formatting to green paid items.

Hi,

 

How about this approach?

Screenshot_1.png

 

Please find it in the attached file.

 

@BSTREET

@Haytham Amairah 

 

This is perfect!  Thank you very much.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies