How do I calculate number of colored cells?

  • Thread starter Thread starter Rui
  • Start date Start date
R

Rui

Hi to master gurus in excel,

I really need your help to solve one issue: how to calculate number of
collor cells?

For instance, from cells range A2:F2 (6 cells),if there are 2 cells filled
in any colors, the counter in cell G2 should be equal to 4(6-2).

Any thoughts?
 
Sub countcoloredcells()
For Each C In Range("a2:f2")
If C.Interior.ColorIndex = -4142 Then mc = mc + 1
Next C
MsgBox mc
End Sub
 
Select your range and this will display the number of cells without color:

Sub ColorMeElmo()
Dim r As Range, Kount As Long
Kount = 0
For Each r In Selection
If r.Interior.ColorIndex = xlNone Then
Kount = Kount + 1
End If
Next
MsgBox Kount
End Sub
 
you can use a udf for this:

Sub test_colorcount()
MsgBox colorcount(Range("I2"), Range("H6:J15"))
End Sub

Function colorcount(base As Range, target As Range) As Long
Dim cell As Range
Dim count As Long
For Each cell In target.Cells
If cell.Interior.Color = base.Interior.Color Then count = count + 1
Next
colorcount = count
End Function

color cell I1 (for my example) say yellow. Put a border arounf H6:J15 so you
can see it clearly, then color a few cell with the same fill as I2. in
another cell put

=colorcount(I2,H6:J15)
 
This is pretty much what Gary did, but I tweaked it a bit. This will give
you the total in the next cell under your range. I'm not sure how you get
your range, but you can select it likes Gary's code or if the range is always
positioned in the same address you can use mine.

Option Explicit

Sub NoColorCellCounter()

Dim MyRange As Range
Dim d As Range
Dim i As Long

MyRange = Range("A1:A10")

i = 0
For Each c In MyRange
If c.Interior.ColorIndex = xlNone Then i = i + 1
Next c

MyRange.Offset(1).Value = i

End Sub

Hope this helps! If so, click "YES" below.
 
Back
Top