count colored cells?

D

DKY

I have a spreadsheet in which there is a calendar. On this calendar ar
cells for the days. In these cells for the days we use colors fo
Vacation (Purple), Holiday (Pink), Unpaid Absences(Green) and Lat
days(Yellow). At the top of the page I want a cell that tells me th
number of Purples are in a range (B10:X66) and how many pinks, green
and yellows there are. Is there a way to use the 'Count' to coun
colors in a range
 
S

soxcpa

You could use this function as well. I used all of Excels standard
colors, you could eliminate those you dont need. Also, the function
does not automatically update when you change a cell background as that
is a formatting change. You will need to recaculate teh worksheet.

Function CountColor(myColorName As String, myRange As Range) As Integer

Dim myColorIndex As Integer

Select Case myColorName
Case "Black"
myColorIndex = 1
Case "Dark Red"
myColorIndex = 9
Case "Red"
myColorIndex = 3
Case "Pink"
myColorIndex = 7
Case "Rose"
myColorIndex = 38
Case "Brown"
myColorIndex = 53
Case "Orange"
myColorIndex = 46
Case "Light Orange"
myColorIndex = 45
Case "Gold"
myColorIndex = 44
Case "Tan"
myColorIndex = 40
Case "Olive Green"
myColorIndex = 52
Case "Dark Yellow"
myColorIndex = 12
Case "Lime"
myColorIndex = 43
Case "Yellow"
myColorIndex = 6
Case "Light Yellow"
myColorIndex = 36
Case "Dark Green"
myColorIndex = 51
Case "Green"
myColorIndex = 10
Case "Sea Green"
myColorIndex = 50
Case "Bright Green"
myColorIndex = 4
Case "Light Green"
myColorIndex = 35
Case "Dark Teal"
myColorIndex = 49
Case "Teal"
myColorIndex = 14
Case "Aqua"
myColorIndex = 42
Case "Turquiose"
myColorIndex = 8
Case "Light Turquoise"
myColorIndex = 34
Case "Dark Blue"
myColorIndex = 11
Case "Blue"
myColorIndex = 5
Case "Light Blue"
myColorIndex = 41
Case "Sky Blue"
myColorIndex = 33
Case "Pale Blue"
myColorIndex = 37
Case "Indigo"
myColorIndex = 55
Case "Blue-Gray"
myColorIndex = 47
Case "Violet"
myColorIndex = 13
Case "Plum"
myColorIndex = 54
Case "Lavender"
myColorIndex = 39
Case "Gray-80%"
myColorIndex = 56
Case "Gray-50%"
myColorIndex = 16
Case "Gray-40%"
myColorIndex = 48
Case "Gray-25%"
myColorIndex = 15
Case "White"
myColorIndex = 2
Case Else
myColorIndex = -4142
End Select

For Each mycell In myRange
If mycell.Interior.ColorIndex = myColorIndex Then CountColor =
CountColor + 1
Next mycell

End Function
 
D

DKY

soxcpa, do I put that whole thing in one cell? I mean, give or take the
ones I don't need?
 
B

Bob Phillips

Did you copy the ColorIndex function into a standard code module.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
D

DKY

DKY said:
That link says that for what I'm looking for, the following should work Code:
Click to expand...

Oh yeah, and I put the code that it says to put into the actual sheet
and I still get the name?
 
D

DKY

nevermind, I got it. for some reason I had to make a module in the
Visual Basic editor and put it in there. Thanks for the help! Its
really appreciated ;)
 
D

DKY

Bob said:
Did you copy the ColorIndex function into a standard code module.

--

HTH

RP
(remove nothere from the email address if mailing direct)


message
Click to expand...

Now I did, I thought originally that it would be okay to copy it into
the 'insert code' when you right click the sheet tab but that didn't
work. Do you know why that is?
 
D

DKY

Something else I just noticed, I have to actually click in the cells and
click in the formula then hit the checkmark to get the numbers to
update. I have automatic updating in the calculations section of the
options checked and F9 doesn't update it nor does it update when I
close and reopen the file. Is there a way around that?
 
B

Bob Phillips

That is the wrong place. In Excel, Alt-F11, in the VBIDE, Insert>Module, and
paste it there.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
D

DKY

Bob said:
That is the wrong place. In Excel, Alt-F11, in the VBIDE, Insert>Module,
and
paste it there.
Click to expand...

Right, that's what I did and it works now but it doesn't refresh. I
have to actually click in the cells and click in the formula then hit
the checkmark to get the numbers to update. I have automatic updating
in the calculations section of the options checked and F9 doesn't
update it nor does it update when I close and reopen the file. Is there
a way around that?
 
B

Bob Phillips

If you read the page it tells you it won't refresh, because changing a
colour does not trigger a recalculation.

You could add

Application.Volatile

at the start of the function, and that will at least get it to respond to
F9.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
B

Bob Phillips

Like this

'---------------------------------------------------------------------
Function ColorIndex(rng As Range, _
Optional text As Boolean = False) As Variant
'---------------------------------------------------------------------
Dim cell As Range, row As Range
Dim i As Long, j As Long
Dim iWhite As Long, iBlack As Long
Dim aryColours As Variant

Application.Volatile

If rng.Areas.Count > 1 Then
ColorIndex = CVErr(xlErrValue)
Exit Function
End If



--

HTH

RP
(remove nothere from the email address if mailing direct)
 
D

DKY

Bob said:
Like this

'---------------------------------------------------------------------
Function ColorIndex(rng As Range, _
Optional text As Boolean = False) As Variant
'---------------------------------------------------------------------
Dim cell As Range, row As Range
Dim i As Long, j As Long
Dim iWhite As Long, iBlack As Long
Dim aryColours As Variant

Application.Volatile

If rng.Areas.Count > 1 Then
ColorIndex = CVErr(xlErrValue)
Exit Function
End If



--

HTH

RP
(remove nothere from the email address if mailing direct)


message
Click to expand...

Hi, I'm an idiot. I appologize, I didn't see your response until
today. I tried it and it doesn't work either. Is there a way I can
upload my file or something and maybe you can take a look at it?
 
B

Bob Phillips

post it to me.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Formatting a Calendar 1
CountIf Condition based on lookup in a list of values 5
Is there a way to count colored cells that are conditionally formatted? 3
count colored cells in excel 5
Count the number of colored cells in a column 5
count cells formated with colors 1
Count Blank Cells & Letter P 6
Is there a way to count the number of different colored cells? 1

Top