SOLVED
Home

If function based on cell color

%3CLINGO-SUB%20id%3D%22lingo-sub-674944%22%20slang%3D%22en-US%22%3EIf%20function%20based%20on%20cell%20color%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-674944%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3EI%20don't%20know%20how%20to%20use%20VBA%20to%20do%20a%20calculation%20based%20on%20cell%20color.%3C%2FP%3E%3CP%3EMy%20goal%20is%20to%20know%20which%20expense%20is%20related%20to%20which%20account%20in%20order%20to%20have%20the%20balance%20of%20each%20account%20separately.%3C%2FP%3E%3CP%3ETo%20do%20so%2C%20in%20the%20cell%20E7%2C%20I%20would%20like%20to%20use%20this%20formula%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIF(cellC7%20is%20blue%20%3B%20then%20E7%2BD7%20%3B%20Else%20E7%3DE6).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20someone%20able%20to%20help%20me%3F%3C%2FP%3E%3CP%3EMany%20thanks%20in%20advance.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-674944%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-675423%22%20slang%3D%22en-US%22%3ERe%3A%20If%20function%20based%20on%20cell%20color%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-675423%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F355933%22%20target%3D%22_blank%22%3E%40Andre-Vassoigneur%3C%2FA%3E%26nbsp%3Bhello.%20Simply%20put%2C%20you%20can't%20do%20this.%20There%20is%20no%20current%20function%20to%20aggregate%20by%20cell%20color.%20There%20are%20some%20workarounds%20though%3A%3C%2FP%3E%3COL%3E%3CLI%3EAdd%20a%20helper%20column*%3C%2FLI%3E%3CLI%3EWrite%20your%20own%20function%3C%2FLI%3E%3CLI%3ETable%20data%20and%20filter%20by%20color%3C%2FLI%3E%3C%2FOL%3E%3CP%3E*%20This%20is%20typically%20the%20best%20way%20as%20it%20gives%20you%20data%20in%20an%20adjacent%20cell%20which%20you%20can%20write%20conditions%20on.%20For%20example%2C%20with%20your%20data%2C%20if%20you%20inserted%20a%20column%20between%20C%20and%20D%2C%20and%20put%20either%20%22Personal%22%20or%20%22Company%22%20in%20D7%3AD14%2C%20you%20would%20have%20a%20column%20you%20could%20use%20as%20a%20condition%20in%20which%20to%20aggregate.%20You%20could%20even%20hide%20that%20column%20afterwards%20if%20you%20didn't%20want%20to%20see%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAssuming%20you%20inserted%20a%20column%2C%20that%20would%20make%20your%20current%20column%20E%20now%20be%20in%20F%2C%20and%20your%20current%20column%20F%20be%20in%20G.%20So%20the%20formulas%20would%20be%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EColumn%20F%20(Personal%20account)%3A%3CBR%20%2F%3E%3DIF(D7%3D%22Personal%22%2CE7%2C0)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EColumn%20G%20(Company%20account)%3A%3CBR%20%2F%3E%3DIF(D7%3D%22Company%22%2CE7%2C0)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYour%20total%20balance%20%5Bcolumn%5D%20would%20stay%20the%20same.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-675933%22%20slang%3D%22en-US%22%3ERe%3A%20If%20function%20based%20on%20cell%20color%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-675933%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F19137%22%20target%3D%22_blank%22%3E%40Zack%20Barresse%3C%2FA%3E%26nbsp%3Bfor%20your%20response.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20thought%20it%20was%20impossible%20too%20but%20I%20would%20like%20to%20be%20sure.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHave%20a%20good%20day%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Andre-Vassoigneur
New Contributor

Hello,

I don't know how to use VBA to do a calculation based on cell color.

My goal is to know which expense is related to which account in order to have the balance of each account separately.

To do so, in the cell E7, I would like to use this formula:

 

IF(cellC7 is blue ; then E7+D7 ; Else E7=E6).

 

Is someone able to help me?

Many thanks in advance.

2 Replies
Solution

@Andre-Vassoigneur hello. Simply put, you can't do this. There is no current function to aggregate by cell color. There are some workarounds though:

  1. Add a helper column*
  2. Write your own function
  3. Table data and filter by color

* This is typically the best way as it gives you data in an adjacent cell which you can write conditions on. For example, with your data, if you inserted a column between C and D, and put either "Personal" or "Company" in D7:D14, you would have a column you could use as a condition in which to aggregate. You could even hide that column afterwards if you didn't want to see it.

 

Assuming you inserted a column, that would make your current column E now be in F, and your current column F be in G. So the formulas would be:

 

Column F (Personal account):
=IF(D7="Personal",E7,0)

 

Column G (Company account):
=IF(D7="Company",E7,0)

 

Your total balance [column] would stay the same.

Thank you @Zack Barresse for your response.

 

I thought it was impossible too but I would like to be sure. 

 

Have a good day  

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