Add only cells colored yellow

Paul18 · #1 02-14-2019, 09:28 AM

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)

Kenneth Hobson · #2 02-14-2019, 06:27 PM

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

alpha · #3 02-17-2019, 09:21 AM

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