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
46 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