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
>
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?
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.
Please Login or Register to view this content.
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.Please Login or Register to view this content.
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
>
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
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?
I don't follow... how do I make this work?
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)
"DKY" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>
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?
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" <[email protected]> wrote in message
news:[email protected]...
>
> Bob Phillips Wrote:
> > That is the wrong place. In Excel, Alt-F11, in the VBIDE, Insert>Module,
> > 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
>
I put it right after the line
Is that the right place to put it? I'm thinking not because the F9 isn't working.Please Login or Register to view this content.
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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>
I put it right after the line
Is that the right place to put it? I'm thinking not because the F9 isn't working.Please Login or Register to view this content.
Does anyone know why this doesn't work for me? Am I putting this code in the wrong place?
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?
post it to me.
--
HTH
RP
(remove nothere from the email address if mailing direct)
"DKY" <[email protected]> wrote in message
news:[email protected]...
>
> 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" <[email protected]> wrote in
> > message
> > news:[email protected]...
> > >
> > > 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
>
I decided to upload it. Let me know your thoughts.
You still out there?
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