#1
|
|||
|
|||
Add only cells colored yellow
How would you add up a column of numbers whereas you would only add cells in the column having a specific color, i.e cell color of yellow (36)
|
#2
|
|||
|
|||
Welcome to the forum!
Code:
'http://www.mrexcel.com/forum/showthread.php?t=566637 Function SumByColor(CellColor As Range, SumRange As Range) ' SumByColor Function ' VBA and Macros for Microsoft Excel by Bill Jelen Mr.Excel, Page 84 ' Modified 07/25/2011 by hiker95 ' =SumByColor(J1,A1:G1) ' =SumByColor(cell_address_where_a_specific_color_is, range_to_Sum) Dim myCell As Range Dim iCol As Integer Dim myTotal iCol = CellColor.Interior.ColorIndex For Each myCell In SumRange If myCell.Interior.ColorIndex = iCol Then myTotal = myTotal + myCell.Value End If Next myCell SumByColor = myTotal End Function |
#3
|
|||
|
|||
1. You don't need a function SumByColor because you don't want to sum numbers, but to count colors !
2. The function CountByColor that you have, is (for Excel) not based on the color but on the colorindex of the cells. The two colours green you use in your table, don't belong to the 56 colors of the colorindex. (For colorindex, see the helpfile of Excel and/or sheet2 in the attachment; the rownumber = the colorindexnumber of the corresponding cell). So: either you must use a function that works for all (millions) colors, or you must use a function that only works with the 56 colors of the colorindex. I choosed the first way, so I have modified the function CountByColor in such a way that you can use it for all colors. See the attachment. Hope this helps. Note: VBA-code can only used in .xlsm-files! |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Extracting colored cells from one spreadsheet into another | Kari Frey | Excel Programming | 7 | 09-15-2016 08:48 AM |
hi I need total job in month In yellow | zEr | Excel | 2 | 05-06-2016 03:55 PM |
Populate D1 if A4 is yellow | Tuph | Excel | 3 | 05-07-2015 02:25 AM |
predefine thick border colored lines for highlighted cells | dylansmith | Excel | 0 | 09-05-2014 07:49 AM |
Text Highlighting in Yellow ??? | mark4man | Publisher | 0 | 12-15-2005 06:46 PM |