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?
See http://www.xldynamic.com/source/xld.ColourCounter.html
--
HTH
RP
(remove nothere from the email address if mailing direct)
"DKY" <[email protected]> wrote in message
news:[email protected]...
>
> 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 workOriginally Posted by Bob Phillips
Problem is, I'm getting a name? at the ColorIndex part.
=SUMPRODUCT(--(ColorIndex(B10:X66)=39))
Oh yeah, and I put the code that it says to put into the actual sheet and I still get the name?That link says that for what I'm looking for, the following should work
Problem is, I'm getting a name? at the ColorIndex part.=SUMPRODUCT(--(ColorIndex(B10:X66)=39))
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
Did you copy the ColorIndex function into a standard code module.
--
HTH
RP
(remove nothere from the email address if mailing direct)
"DKY" <[email protected]> wrote in message
news:[email protected]...
>
> Bob Phillips Wrote:
> > See http://www.xldynamic.com/source/xld.ColourCounter.html
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "DKY" <[email protected]> wrote in
> > message
> > news:[email protected]...
> > >
> > > 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?
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?
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
soxcpa, do I put that whole thing in one cell? I mean, give or take the ones I don't need?
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.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks