I am using the following functions together to get an average of colored cells.
Function CountCellsByColor(rData As Range, cellRefColor As Range) As Long
'(Range,Cell of Color)
Dim indRefColor As Long
Dim cellCurrent As Range
Dim cntRes As Long
Application.Volatile True
cntRes = 0
indRefColor = cellRefColor.Cells(1, 1).Interior.Color
For Each cellCurrent In rData
If indRefColor = cellCurrent.Interior.Color Then
cntRes = cntRes + 1
End If
Next cellCurrent
CountCellsByColor = cntRes
Application.Volatile False
End Function
Function SumCellsByColor(rData As Range, cellRefColor As Range)
'(Range,Cell of Color)
Dim indRefColor As Long
Dim cellCurrent As Range
Dim sumRes
Application.Volatile True
sumRes = 0
indRefColor = cellRefColor.Cells(1, 1).Interior.Color
For Each cellCurrent In rData
If indRefColor = cellCurrent.Interior.Color Then
sumRes = WorksheetFunction.Sum(cellCurrent, sumRes)
End If
Next cellCurrent
SumCellsByColor = sumRes
Application.Volatile False
End Function
I am using:
Sub ColorBlue()
Application.DisplayAlerts = False
Selection.Interior.Color = 250
Range("A14:M14").CalculateRowMajorOrder
Application.DisplayAlerts = False
End Sub
to change the cell color to the desired color and I would like it to "refresh" the formulas in Range(A14:M14). I have tried Calculate and CalculateRowMajorOrder. CalculateRowMajorOrder changed the values the first time but when I tried on a different cell it didn't update again. Thoughts on how to make this work?
Bookmarks