Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
DKY
 
Posts: n/a
Default count colored cells?


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


--
DKY
------------------------------------------------------------------------
DKY's Profile: http://www.excelforum.com/member.php...o&userid=14515
View this thread: http://www.excelforum.com/showthread...hreadid=499846

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bob Phillips
 
Posts: n/a
Default count colored cells?

See http://www.xldynamic.com/source/xld.ColourCounter.html

--

HTH

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


"DKY" wrote in message
...

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


--
DKY
------------------------------------------------------------------------
DKY's Profile:

http://www.excelforum.com/member.php...o&userid=14515
View this thread: http://www.excelforum.com/showthread...hreadid=499846



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
 
Posts: n/a
Default count colored cells?

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

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
DKY
 
Posts: n/a
Default count colored cells?


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


--
DKY
------------------------------------------------------------------------
DKY's Profile: http://www.excelforum.com/member.php...o&userid=14515
View this thread: http://www.excelforum.com/showthread...hreadid=499846

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
DKY
 
Posts: n/a
Default count colored cells?


Bob Phillips Wrote:
See http://www.xldynamic.com/source/xld.ColourCounter.html

--

HTH

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


"DKY" wrote in
message
...

I have a spreadsheet in which there is a calendar. On this calendar

are
cells for the days. In these cells for the days we use colors for
Vacation (Purple), Holiday (Pink), Unpaid Absences(Green) and Late
days(Yellow). At the top of the page I want a cell that tells me

the
number of Purples are in a range (B10:X66) and how many pinks,

greens
and yellows there are. Is there a way to use the 'Count' to count
colors in a range?


--
DKY

------------------------------------------------------------------------
DKY's Profile:

http://www.excelforum.com/member.php...o&userid=14515
View this thread:

http://www.excelforum.com/showthread...hreadid=499846


That link says that for what I'm looking for, the following should
work

Code:
--------------------
=SUMPRODUCT(--(ColorIndex(B10:X66)=39))
--------------------

Problem is, I'm getting a name? at the ColorIndex part.


--
DKY
------------------------------------------------------------------------
DKY's Profile: http://www.excelforum.com/member.php...o&userid=14515
View this thread: http://www.excelforum.com/showthread...hreadid=499846



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bob Phillips
 
Posts: n/a
Default count colored cells?

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

--

HTH

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


"DKY" wrote in message
...

Bob Phillips Wrote:
See http://www.xldynamic.com/source/xld.ColourCounter.html

--

HTH

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


"DKY" wrote in
message
...

I have a spreadsheet in which there is a calendar. On this calendar

are
cells for the days. In these cells for the days we use colors for
Vacation (Purple), Holiday (Pink), Unpaid Absences(Green) and Late
days(Yellow). At the top of the page I want a cell that tells me

the
number of Purples are in a range (B10:X66) and how many pinks,

greens
and yellows there are. Is there a way to use the 'Count' to count
colors in a range?


--
DKY

------------------------------------------------------------------------
DKY's Profile:

http://www.excelforum.com/member.php...o&userid=14515
View this thread:

http://www.excelforum.com/showthread...hreadid=499846


That link says that for what I'm looking for, the following should
work

Code:
--------------------
=SUMPRODUCT(--(ColorIndex(B10:X66)=39))
--------------------

Problem is, I'm getting a name? at the ColorIndex part.


--
DKY
------------------------------------------------------------------------
DKY's Profile:

http://www.excelforum.com/member.php...o&userid=14515
View this thread: http://www.excelforum.com/showthread...hreadid=499846



  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
DKY
 
Posts: n/a
Default count colored cells?


DKY Wrote:
That link says that for what I'm looking for, the following should work

Code:
--------------------
=SUMPRODUCT(--(ColorIndex(B10:X66)=39))

--------------------

Problem is, I'm getting a name? at the ColorIndex part.


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


--
DKY
------------------------------------------------------------------------
DKY's Profile: http://www.excelforum.com/member.php...o&userid=14515
View this thread: http://www.excelforum.com/showthread...hreadid=499846

  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
DKY
 
Posts: n/a
Default count colored cells?


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 ;)


--
DKY
------------------------------------------------------------------------
DKY's Profile: http://www.excelforum.com/member.php...o&userid=14515
View this thread: http://www.excelforum.com/showthread...hreadid=499846

  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
DKY
 
Posts: n/a
Default count colored cells?


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

--

HTH

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


"DKY" wrote in
message
...

Bob Phillips Wrote:
See http://www.xldynamic.com/source/xld.ColourCounter.html

--

HTH

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


"DKY" wrote in
message
...

I have a spreadsheet in which there is a calendar. On this

calendar
are
cells for the days. In these cells for the days we use colors

for
Vacation (Purple), Holiday (Pink), Unpaid Absences(Green) and

Late
days(Yellow). At the top of the page I want a cell that tells

me
the
number of Purples are in a range (B10:X66) and how many pinks,
greens
and yellows there are. Is there a way to use the 'Count' to

count
colors in a range?


--
DKY


------------------------------------------------------------------------
DKY's Profile:
http://www.excelforum.com/member.php...o&userid=14515
View this thread:
http://www.excelforum.com/showthread...hreadid=499846


That link says that for what I'm looking for, the following should
work

Code:
--------------------
=SUMPRODUCT(--(ColorIndex(B10:X66)=39))
--------------------

Problem is, I'm getting a name? at the ColorIndex part.


--
DKY

------------------------------------------------------------------------
DKY's Profile:

http://www.excelforum.com/member.php...o&userid=14515
View this thread:

http://www.excelforum.com/showthread...hreadid=499846


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?


--
DKY
------------------------------------------------------------------------
DKY's Profile: http://www.excelforum.com/member.php...o&userid=14515
View this thread: http://www.excelforum.com/showthread...hreadid=499846

  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
DKY
 
Posts: n/a
Default count colored cells?


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?


--
DKY
------------------------------------------------------------------------
DKY's Profile: http://www.excelforum.com/member.php...o&userid=14515
View this thread: http://www.excelforum.com/showthread...hreadid=499846



  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ardilla
 
Posts: n/a
Default count colored cells?


I don't follow... how do I make this work?


--
Ardilla
------------------------------------------------------------------------
Ardilla's Profile: http://www.excelforum.com/member.php...o&userid=30328
View this thread: http://www.excelforum.com/showthread...hreadid=499846

  #12   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bob Phillips
 
Posts: n/a
Default count colored cells?

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

--

HTH

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


"DKY" wrote in message
...

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?


--
DKY
------------------------------------------------------------------------
DKY's Profile:

http://www.excelforum.com/member.php...o&userid=14515
View this thread: http://www.excelforum.com/showthread...hreadid=499846



  #13   Report Post  
Posted to microsoft.public.excel.worksheet.functions
DKY
 
Posts: n/a
Default count colored cells?


Bob Phillips Wrote:
That is the wrong place. In Excel, Alt-F11, in the VBIDE, InsertModule,
and
paste it there.


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?


--
DKY
------------------------------------------------------------------------
DKY's Profile: http://www.excelforum.com/member.php...o&userid=14515
View this thread: http://www.excelforum.com/showthread...hreadid=499846

  #14   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bob Phillips
 
Posts: n/a
Default count colored cells?

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)


"DKY" wrote in message
...

Bob Phillips Wrote:
That is the wrong place. In Excel, Alt-F11, in the VBIDE, InsertModule,
and
paste it there.


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?


--
DKY
------------------------------------------------------------------------
DKY's Profile:

http://www.excelforum.com/member.php...o&userid=14515
View this thread: http://www.excelforum.com/showthread...hreadid=499846



  #15   Report Post  
Posted to microsoft.public.excel.worksheet.functions
DKY
 
Posts: n/a
Default count colored cells?


I put it right after the line

Code:
--------------------
Function ColorIndex(rng As Range, _
Optional text As Boolean = False) As Variant
--------------------

Is that the right place to put it? I'm thinking not because the F9
isn't working.


--
DKY
------------------------------------------------------------------------
DKY's Profile: http://www.excelforum.com/member.php...o&userid=14515
View this thread: http://www.excelforum.com/showthread...hreadid=499846



  #16   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bob Phillips
 
Posts: n/a
Default count colored cells?

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)


"DKY" wrote in message
...

I put it right after the line

Code:
--------------------
Function ColorIndex(rng As Range, _
Optional text As Boolean = False) As Variant
--------------------

Is that the right place to put it? I'm thinking not because the F9
isn't working.


--
DKY
------------------------------------------------------------------------
DKY's Profile:

http://www.excelforum.com/member.php...o&userid=14515
View this thread: http://www.excelforum.com/showthread...hreadid=499846



  #17   Report Post  
Posted to microsoft.public.excel.worksheet.functions
DKY
 
Posts: n/a
Default count colored cells?


DKY Wrote:
I put it right after the line

Code:
--------------------
Function ColorIndex(rng As Range, _

Optional text As Boolean = False) As Variant

--------------------

Is that the right place to put it? I'm thinking not because the F9
isn't working.



Does anyone know why this doesn't work for me? Am I putting this code
in the wrong place?


--
DKY
------------------------------------------------------------------------
DKY's Profile: http://www.excelforum.com/member.php...o&userid=14515
View this thread: http://www.excelforum.com/showthread...hreadid=499846

  #18   Report Post  
Posted to microsoft.public.excel.worksheet.functions
DKY
 
Posts: n/a
Default count colored cells?


Bob Phillips Wrote:
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)


"DKY" wrote in
message
...

I put it right after the line

Code:
--------------------
Function ColorIndex(rng As Range, _
Optional text As Boolean = False) As Variant
--------------------

Is that the right place to put it? I'm thinking not because the F9
isn't working.


--
DKY

------------------------------------------------------------------------
DKY's Profile:

http://www.excelforum.com/member.php...o&userid=14515
View this thread:

http://www.excelforum.com/showthread...hreadid=499846


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?


--
DKY
------------------------------------------------------------------------
DKY's Profile: http://www.excelforum.com/member.php...o&userid=14515
View this thread: http://www.excelforum.com/showthread...hreadid=499846

  #19   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bob Phillips
 
Posts: n/a
Default count colored cells?

post it to me.

--

HTH

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


"DKY" wrote in message
...

Bob Phillips Wrote:
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)


"DKY" wrote in
message
...

I put it right after the line

Code:
--------------------
Function ColorIndex(rng As Range, _
Optional text As Boolean = False) As Variant
--------------------

Is that the right place to put it? I'm thinking not because the F9
isn't working.


--
DKY

------------------------------------------------------------------------
DKY's Profile:

http://www.excelforum.com/member.php...o&userid=14515
View this thread:

http://www.excelforum.com/showthread...hreadid=499846


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?


--
DKY
------------------------------------------------------------------------
DKY's Profile:

http://www.excelforum.com/member.php...o&userid=14515
View this thread: http://www.excelforum.com/showthread...hreadid=499846



  #20   Report Post  
Posted to microsoft.public.excel.worksheet.functions
DKY
 
Posts: n/a
Default count colored cells?


I decided to upload it. Let me know your thoughts.


+-------------------------------------------------------------------+
|Filename: Calendar.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4229 |
+-------------------------------------------------------------------+

--
DKY
------------------------------------------------------------------------
DKY's Profile: http://www.excelforum.com/member.php...o&userid=14515
View this thread: http://www.excelforum.com/showthread...hreadid=499846



  #21   Report Post  
Posted to microsoft.public.excel.worksheet.functions
DKY
 
Posts: n/a
Default count colored cells?


You still out there?


--
DKY
------------------------------------------------------------------------
DKY's Profile: http://www.excelforum.com/member.php...o&userid=14515
View this thread: http://www.excelforum.com/showthread...hreadid=499846

  #22   Report Post  
Posted to microsoft.public.excel.worksheet.functions
 
Posts: n/a
Default count colored cells?

Sorry about the response time...I did not see your message. The
ColorCount is a Function. Open the Visual Basic Editor
(Tools-Macro-Visual Basic Editor --or-- ALT-F11). Choose Insert-Module
(not class module). Copy and paste the code in the last post and then
you can use the function in your spreadsheet using the following
syntax:
=CountColor(ColorName,Range)

=CountColor("Dark Red",A1:B5)

will count the Dark Red backgrounds in the range A1 to B5.

One Caution:
The line:
If mycell.Interior.ColorIndex = myColorIndex Then CountColor =
CountColor + 1

is all one line. It might not paste that way.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
checking that cells have a value before the workbook will close kcdonaldson Excel Worksheet Functions 8 December 5th 05 05:57 PM
how 2 Count number of cells that have specific condition format? daveydavey Excel Worksheet Functions 2 May 4th 05 02:06 PM
Count cells in one range based on parameters in another range dave roth Excel Worksheet Functions 2 March 29th 05 05:33 PM
How do I count shaded cells Randy Excel Worksheet Functions 19 February 4th 05 12:35 AM
Count or sum colored cells brightgirl Excel Worksheet Functions 2 December 7th 04 04:34 PM


All times are GMT +1. The time now is 11:40 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"