If function based on cell color

[Andre-Vassoigneur]

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 · Accepted 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.

[#3]

Thank you @Zack Barresse for your response.

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

Have a good day