Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting Formats
Hello, I have a work sheet which has a list of things running from A8 down to
A400. They have been manually classified by use of the cell fill colour. Is there a way that I can show the number of green rows or red row etc? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting Formats
You need a macro to do this.
try this UDF : Copy and paste the below code to your module in VBE in cell B8, place this formula =CountColor($A$8,$A$8:$A$400) assuming that A8 is green, this will count all the green format cells repeat this for red by changing the lookup value in the formula Pls note that this will not work if you any of your color format in these cells used conditional formatting. Function CountColor(rColor As Range, rRange As Range, Optional SUM As Boolean) Dim rCell As Range Dim lCol As Long Dim vResult 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 CountColor = vResult End Function Does this do what you want? HTH -- If this posting was helpful, please click on the Yes button Thank You cheers, "CraigAllen" wrote: Hello, I have a work sheet which has a list of things running from A8 down to A400. They have been manually classified by use of the cell fill colour. Is there a way that I can show the number of green rows or red row etc? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting Formats
You need to use VBA code. You can't do it with Excel's built in
functions. See http://www.cpearson.com/excel/colors.aspx for details, examples, and downloadable code. Cordially, Chip Pearson Microsoft MVP Excel Product Group Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Fri, 19 Dec 2008 22:40:05 -0800, CraigAllen wrote: Hello, I have a work sheet which has a list of things running from A8 down to A400. They have been manually classified by use of the cell fill colour. Is there a way that I can show the number of green rows or red row etc? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying formats - column widths, formats, outlining to worksheets | Excel Worksheet Functions | |||
Counting cell formats | Excel Discussion (Misc queries) | |||
Counting Conditional Formats | New Users to Excel | |||
Conditional formats- paste special formats? | Excel Discussion (Misc queries) | |||
paste conditional formats as formats | Excel Discussion (Misc queries) |