Forum Discussion

Ronald_Hoerner's avatar
Ronald_Hoerner
Copper Contributor
Jan 29, 2023
Solved

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

3 Replies

  • Harun24HR's avatar
    Harun24HR
    Bronze 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

Resources