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?
--
Thanks in advance!

Sub countcoloredcells()
For Each C In Range("a2:f2")
If C.Interior.ColorIndex = -4142 Then mc = mc + 1
Next C
MsgBox mc
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Rui" <(E-Mail Removed)> wrote in message
news:102253A9-4D4C-47F3-8D97-(E-Mail Removed)...
> 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?
> --
> Thanks in advance!

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
--
Gary''s Student - gsnu200909


"Rui" wrote:

> 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?
> --
> Thanks in advance!

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)







"Rui" <(E-Mail Removed)> wrote in message
news:102253A9-4D4C-47F3-8D97-(E-Mail Removed)...
> 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?
> --
> Thanks in advance!

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.
--
Cheers,
Ryan


"Rui" wrote:

> 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?
> --
> Thanks in advance!