Forum Discussion
count cells by color for multiple colors in range
So I have the macro to count cells by color already and have it set up to count cells with 1 color in a range. is there away to count multiple colors for the same range? I'm trying to calculate t...
You could use a similar macro/custom function, but add 1 to the count in the loop through the cells if
mycell.Interior.ColorIndex <> xlColorIndexNone
where mycell is the variable that refers to the cell in the loop.
This is the function i had found and used online for the count cells
I'm not really sure how i would edit it to add what i need lol
Function SumCellsByColor(data_range As Range, cell_color As Range)
Dim indRefColor As Long
Dim cellCurrent As Range
Dim sumRes
Application.Volatile
sumRes = 0
indRefColor = cell_color.Cells(1, 1).Interior.Color
For Each cellCurrent In data_range
If indRefColor = cellCurrent.Interior.Color Then
sumRes = WorksheetFunction.Sum(cellCurrent, sumRes)
End If
Next cellCurrent
SumCellsByColor = sumRes
End Function
I'm not really sure how i would edit it to add what i need lol
Function SumCellsByColor(data_range As Range, cell_color As Range)
Dim indRefColor As Long
Dim cellCurrent As Range
Dim sumRes
Application.Volatile
sumRes = 0
indRefColor = cell_color.Cells(1, 1).Interior.Color
For Each cellCurrent In data_range
If indRefColor = cellCurrent.Interior.Color Then
sumRes = WorksheetFunction.Sum(cellCurrent, sumRes)
End If
Next cellCurrent
SumCellsByColor = sumRes
End Function
Here you go:
Function SumNoFillCells(data_range As Range) As Double Dim cellCurrent As Range Dim sumRes As Double Application.Volatile sumRes = 0 For Each cellCurrent In data_range If cellCurrent.Interior.ColorIndex = xlColorIndexNone Then sumRes = WorksheetFunction.Sum(cellCurrent, sumRes) End If Next cellCurrent SumNoFillCells = sumRes End FunctionExample of usage:
=SumNoFillCells(E1:E6)
