Forum Discussion
cell format info in formulas
I wrote a small routine to count the number of cell in a range (calendar_ronald) that match the color of a cell (cell_color).
Sub CountCellsByColor()
Dim calendar_ronald As Range
Dim cell_color As Range
Dim color_count As Integer
Set calendar_ronald = Range("A1:D12")
Set cell_color = Range("E1")
color_count = 0
For Each cell In calendar_ronald
If cell.Interior.Color = cell_color.Interior.Color Then
color_count = color_count + 1
End If
Next cell
MsgBox "The number of cells in the given area with the specified color is: " & color_count
End Sub
1st question: How do I get the result of this macro (color_count) into a cell?
I now tried to do a formula for this like:
=SUMPRODUCT(--(calendar_ronald.Font.Color = cell_color.Font.Color), --(calendar_ronald <> ""))
but when introducing into a cell it gives the error message #FIELD. I suppose it does not understand xx.Font.Color
How can I solve this?
Thanks Ronald
- No. Function can't work with color.
3 Replies
- Harun24HRBronze Contributor
Ronald_Hoerner To get count result in cell replace below line-
MsgBox "The number of cells in the given area with the specified color is: " & color_count
With
Range("F1") = color_count
- Ronald_HoernerCopper Contributor
- Harun24HRBronze ContributorNo. Function can't work with color.