Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
checking that cells have a value before the workbook will close | Excel Worksheet Functions | |||
how 2 Count number of cells that have specific condition format? | Excel Worksheet Functions | |||
Count cells in one range based on parameters in another range | Excel Worksheet Functions | |||
How do I count shaded cells | Excel Worksheet Functions | |||
Count or sum colored cells | Excel Worksheet Functions |