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
Help with an IF AND formula
aanaya6 in Excel on
3 Replies
IF FUNCTION ISN'T WORKING NO MATTER HOW SIMPLE THE COMMAND IS
thomasea in Excel on
6 Replies
not able to type number and text in same cell?
Deleted in Excel on
2 Replies
Which formula to use
Ramon Haagen in Excel on
2 Replies
Automatic coloring sunburst chart
stefan645 in Excel on
2 Replies