Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Format Types
How do I count the number of cells in a worksheet that contain a certain
format, i.e. shaded yellow? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Format Types
There is no built-in excel function to do this. But you can use a UDF that
looks at the range and returns the sum of color. But that function will not recalculate if you change color. Every time you change the color you will need to recalculate or wait excel to recalculate... To install the UDF (User Defined function). From workbook launch VBE using Alt+F11. From menu Insert a Module and paste the below function.Close and get back to workbook and try the below formula. Function ColorSum(varRange As Range, varColor As Variant) Dim arrTemp As Variant Dim varTemp As Variant For Each cell In varRange varTemp = cell.Interior.ColorIndex If varTemp = -4142 Then varTemp = 0 If varTemp = varColor Then ColorSum = ColorSum + 1 Next End Function To use the as a formula in range A1:A10 try the below; which will count the number of blue cells....The second argument denotes the colorindex... Below denotes the other colors... =colorsum(A1:A10,6) 0 - No Color 1 - Black 2 - White 3 - Red 4 - Green 5 - Blue 6 - Yellow 7 - Magenta 8 - Cyan If this post helps click Yes --------------- Jacob Skaria "TByersTX" wrote: How do I count the number of cells in a worksheet that contain a certain format, i.e. shaded yellow? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Format Types
Hi,
see CPearson Web http://www.cpearson.com/excel/colors.aspx "TByersTX" wrote: How do I count the number of cells in a worksheet that contain a certain format, i.e. shaded yellow? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Format Types
Thanks Jacob,
The first time I followed your directions, it worked like a charm. When I retrieve the new macro enabled worksheet, the formula gives me the "#NAME?" error. I can retrieve the old worksheet and start over, and it works like a charm again. Every time after saving, I get the same error. Any ideas? "Jacob Skaria" wrote: There is no built-in excel function to do this. But you can use a UDF that looks at the range and returns the sum of color. But that function will not recalculate if you change color. Every time you change the color you will need to recalculate or wait excel to recalculate... To install the UDF (User Defined function). From workbook launch VBE using Alt+F11. From menu Insert a Module and paste the below function.Close and get back to workbook and try the below formula. Function ColorSum(varRange As Range, varColor As Variant) Dim arrTemp As Variant Dim varTemp As Variant For Each cell In varRange varTemp = cell.Interior.ColorIndex If varTemp = -4142 Then varTemp = 0 If varTemp = varColor Then ColorSum = ColorSum + 1 Next End Function To use the as a formula in range A1:A10 try the below; which will count the number of blue cells....The second argument denotes the colorindex... Below denotes the other colors... =colorsum(A1:A10,6) 0 - No Color 1 - Black 2 - White 3 - Red 4 - Green 5 - Blue 6 - Yellow 7 - Magenta 8 - Cyan If this post helps click Yes --------------- Jacob Skaria "TByersTX" wrote: How do I count the number of cells in a worksheet that contain a certain format, i.e. shaded yellow? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 error "some chart types cannot be combined with other chart types. Select a different chart types" | Charts and Charting in Excel | |||
Count No of Types. | Excel Worksheet Functions | |||
Count cells by format | Excel Worksheet Functions | |||
How can I format the #of decimals, field types, etc. automaticall. | Excel Discussion (Misc queries) | |||
Count format changes | Excel Discussion (Misc queries) |