How to subtotal based on cell color?

[alexduy]

ExcelForumExample.xlsx

Is it possible to subtotal based on color? Attached is an example excel spreadsheet.
The "Example" tab is a sample data, and the "Finish" tab is how I would like it to be.
I appreciate the help, thanks.

[Danerida]

Are you using Conditional Formatting? What are your criteria for the coloring of your data? If you are using conditional formatting, you can apply the same criteria to a SUMIF to achieve this (it won't be exactly like a SUBTOTAL layout, but will be close).

No formula can check the formatting of cells.

[alansidman]

Look at this video and use the concepts explained herein:

http://www.datapigtechnologies.com/f...rtonColor.html

[stanleydgromjr]

alexduy,

Thanks for the workbook.

Detach/open workbook SubtotalByColor - alexduy - EF1033224 - SDG15.xlsm and run the SubtotalByColor macro.

[alexduy]

You guys are awsome. All methods provided works, especially thanks to Stanley for the awsome code.

[stanleydgromjr]

alexduy,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.

[alansidman]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[xenixman]

If you need something that is not a Macro, you can try this.

It is kind of a cheat, as it uses the old xl 4 macro function Get.cell which can not be called directly from a formula, but can be used with a Defined Name.

I don't remember the exact sequence for 2003, but I believe it should be something like:

On the Task Bar:
1) Go to Insert
2) select Name
3) select Define.
4) In the Name box, enter a name: Get_Cell_Color_L
5) In the Refers to box, enter the following:

=GET.CELL(63,OFFSET(INDIRECT("RC",FALSE),0,-1))

6) Click OK
7) Click Close

Now, to get the color of the cell to the LEFT, (Hence the Get_Cell_Color_L) simply enter:

=Get_Cell_Color_L


If you need to get the cell color in any of the other directions then:

Right: =Get_Cell_Color_R

=GET.CELL(63,OFFSET(INDIRECT("RC",FALSE),0,1))

Up: =Get_Cell_Color_U

=GET.CELL(63,OFFSET(INDIRECT("RC",FALSE),-1,0))

Down: =Get_Cell_Color_D

=GET.CELL(63,OFFSET(INDIRECT("RC",FALSE),1,0))


For a more info see this thread:

http://www.mrexcel.com/forum/excel-q...arguments.html

Just one Caveat - This will not work if the cell was colored with Conditional Formatting
there may be a way, but I have not played with it enough to find out.