Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Suggestion for a macro?
Hey everyone,
Ok this is going to be a detailed description so if it is a little confusing so bare with me. My macro experience is nothing more than beginner also so any sort of help will be much appreciated. What I have is a building entry pass list of about 250 people and 5 different categories that people are defined with. What I need is a macro that will count the number of cells, that have a specific background color and display the results in a single cell below the range. I.E. One of the categories is "Need Deleted" and has a red background color. There are 5 different colors and 6 different columns. Each column of names has a spacer column between it (This is where some of my problems arise). The way the spread sheet is setup, the results are in a range of there own with the specific amount for each color displays for the corresponding column. As of right now i have a COLORFUNCTION equation in there that counts them but it doesn't automatically update the result when you change the color. (EX. =ColorFunction(D57,E5:E54,FALSE)). A guy I work with found this macro but I can't seem to get it working because, well I just don't have that much experience with macros (It is a general macro and nothing is defined specifically yet.): Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean) Dim rCell As Range Dim lCol As Long Dim vResult '''''''''''''''''''''''''''''''''''''' 'Written by Ozgrid Business Applications 'www.ozgrid.com 'Sums or counts cells based on a specified fill color. ''''''''''''''''''''''''''''''''''''''' lCol = rColor.Interior.ColorIndex If SUM = True Then For Each rCell In rRange If rCell.Interior.ColorIndex = lCol Then vResult = WorksheetFunction.SUM(rCell, vResult) End If Next rCell Else For Each rCell In rRange If rCell.Interior.ColorIndex = lCol Then vResult = 1 + vResult End If Next rCell End If ColorFunction = vResult End Function I took one look at that and got confused about all the ranges and such. I am wondering if anyone has any ideas on if i can use this macro or if there is a better route to the solution, or if I am just using the colorfunction wrong and it is on my end that it is not automatically updating when i change the color. Thanks a lot in advance for any help received. Cheers, Steve |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Suggestion for a macro?
Well the first question is: did you follow the links at ozgrid.com explaining
the colorindex and how that figures into the macro? Some info on colorindex he http://www.cpearson.com/excel/colors.htm Basically, the color of the cells you want to count has a number associated with it; that number (the color index of the cell shading) needs to be specified. Dave -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "SteveK" wrote: Hey everyone, Ok this is going to be a detailed description so if it is a little confusing so bare with me. My macro experience is nothing more than beginner also so any sort of help will be much appreciated. What I have is a building entry pass list of about 250 people and 5 different categories that people are defined with. What I need is a macro that will count the number of cells, that have a specific background color and display the results in a single cell below the range. I.E. One of the categories is "Need Deleted" and has a red background color. There are 5 different colors and 6 different columns. Each column of names has a spacer column between it (This is where some of my problems arise). The way the spread sheet is setup, the results are in a range of there own with the specific amount for each color displays for the corresponding column. As of right now i have a COLORFUNCTION equation in there that counts them but it doesn't automatically update the result when you change the color. (EX. =ColorFunction(D57,E5:E54,FALSE)). A guy I work with found this macro but I can't seem to get it working because, well I just don't have that much experience with macros (It is a general macro and nothing is defined specifically yet.): Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean) Dim rCell As Range Dim lCol As Long Dim vResult '''''''''''''''''''''''''''''''''''''' 'Written by Ozgrid Business Applications 'www.ozgrid.com 'Sums or counts cells based on a specified fill color. ''''''''''''''''''''''''''''''''''''''' lCol = rColor.Interior.ColorIndex If SUM = True Then For Each rCell In rRange If rCell.Interior.ColorIndex = lCol Then vResult = WorksheetFunction.SUM(rCell, vResult) End If Next rCell Else For Each rCell In rRange If rCell.Interior.ColorIndex = lCol Then vResult = 1 + vResult End If Next rCell End If ColorFunction = vResult End Function I took one look at that and got confused about all the ranges and such. I am wondering if anyone has any ideas on if i can use this macro or if there is a better route to the solution, or if I am just using the colorfunction wrong and it is on my end that it is not automatically updating when i change the color. Thanks a lot in advance for any help received. Cheers, Steve |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Suggestion for a macro?
Dave,
I got all the integers relating to the specific color, but I don't know how to incorporate this into the macro. I tried keeping it simple and only doing one column and counting only one color, but I don't know how to start the macro to see if it works (can't just click run and select the macro). I'm guessing that it will run automatically once I figure out how to do get it to work. I tried one of the macros from the site below and this is what I have come up with so far. Check it out: Function CountByColor(InRange As Range, _ WhatColorIndex As Integer, _ Optional OfText As Boolean = False) As Long ' ' This function returns the number of cells in InRange with ' a background color, or if OfText is True a font color, ' equal to WhatColorIndex. ' Dim Rng As Range Application.Volatile True 'not exactly sure what this is for InRange = Range(Cells(h, 5), Cells(h, 55)) 'Range to look for the cells WhatColorIndex = 3 Rng = Cells(h, 67) 'I think this is the resulting cell? For Each Rng In InRange.Cells If OfText = True Then CountByColor = CountByColor - _ (Rng.Font.ColorIndex = WhatColorIndex) 'Thinking about deleting the If function because it doesnt apply Else CountByColor = CountByColor - _ (Rng.Interior.ColorIndex = WhatColorIndex) End If Next Rng End Function "Dave F" wrote: Well the first question is: did you follow the links at ozgrid.com explaining the colorindex and how that figures into the macro? Some info on colorindex he http://www.cpearson.com/excel/colors.htm Basically, the color of the cells you want to count has a number associated with it; that number (the color index of the cell shading) needs to be specified. Dave -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Newbie seeks suggestion on recording new macro to print sheets | New Users to Excel | |||
Suggestion | Excel Worksheet Functions | |||
Suggestion about a formula | Excel Discussion (Misc queries) | |||
Suggestion about a formula | Excel Discussion (Misc queries) | |||
Suggestion on this one? | Excel Discussion (Misc queries) |