in the new attached example, in columns K and L, i used the UDF to get the cell color.
=IFERROR(IF(SEARCH("Person 1";C6)>0;SUMIFBYCOLOR(D6:E6;3;D6:E6;FALSE);0);0)-this formula in column G searches first cell for "Person 1" and sums the green range if the cell colour is 3 (means red to excel) all results are totalised in cell G6 for person 1.
Same for person 2 in column H...
This formula in cell J2:
=COUNTIFS(C$6:C$20;C$6;L$6:L$20;"=3")+COUNTIFS(C$6:C$20;C$6;K$6:K$20;"=3")
counts how many cells are red (=3) for C$6 (person 1), (depending to ranges L$6:L$20 and K$6:K$20)
This formula in cell J3:
=COUNTIFS(C$6:C$20;C$7;L$6:L$20;"=3")+COUNTIFS(C$6:C$20;C$7;K$6:K$20;"=3")
counts how many cells are red (=3) for C$7 (person 2), (depending to ranges L$6:L$20 and K$6:K$20). In the example, the result is 0, because there are no red cells for person 2...
but if you change =3 to =43, in J3 formula, you will get the number of cells that are color 43 for person 2... and so on, ....
This formula will get the cell color:
=CELLCOLORINDEX(D6;FALSE)
This formula will get the font color used in that cell:
=CELLCOLORINDEX(D6;TRUE)
NOTE: When you change the background or font color of a cell, Excel does not consider this to be changing the value of the cell. Therefore, it will not recalculate the worksheet, nor will it trigger a Worksheet_Change event procedure. This means that the values returned by these functions may not be correct immediately after you change the color of a cell. They will not return an updated value until you recalculate the worksheet by pressing ALT+F9 or by changing the actual value of a cell.
NOTE: These functions will not detect colors that are applied by Conditional Formatting.
If you want to use a UDF in more than one workbook, you can save your functions in your own custom add-in. Simply save your excel file that contains your VBA functions as an add-in file (.xla). Then load the add-in (Tools > Add-Ins...). Warning! Be careful about using custom functions in spreadsheets that you need to share with others. If they don't have your add-in, the functions will not work when they use the spreadsheet.
|