Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to count cells colored red?
I believe this question has been asked and answered before, but sigh, I did
not pay attention at the time, and it is hard to find the threads now. Using an Excel formula and no VBA, how can I count the cells that are colored red due to font formatting (e.g, using Font.ColorIndex in VBA)? The CELL("color") function does not seem to give distinctive results in that case. (Only if negative values are colored, which is not my situation.) I do have an alternative: I can write a UDF to count them. But I wonder if there is any way to do this with Excel formulas alone. FYI, my UDF is below. Any suggestions for improvements would be welcomed. For example, I do not like hardcoding the color index 3 (red). Function countRed(rng As Range) As Double Dim cell As Range For Each cell In rng If cell.Font.ColorIndex = 3 Then countRed = countRed + 1 Next cell End Function |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to count cells colored red?
hi
i know of no built in functions that will do colors so you UDF is about the only way. you might want to look at chip's site. he has a number of color functions to count, sum, sort, other. http://cpearson.com/excel/colors.htm Regards FSt1 "JoeU2004" wrote: I believe this question has been asked and answered before, but sigh, I did not pay attention at the time, and it is hard to find the threads now. Using an Excel formula and no VBA, how can I count the cells that are colored red due to font formatting (e.g, using Font.ColorIndex in VBA)? The CELL("color") function does not seem to give distinctive results in that case. (Only if negative values are colored, which is not my situation.) I do have an alternative: I can write a UDF to count them. But I wonder if there is any way to do this with Excel formulas alone. FYI, my UDF is below. Any suggestions for improvements would be welcomed. For example, I do not like hardcoding the color index 3 (red). Function countRed(rng As Range) As Double Dim cell As Range For Each cell In rng If cell.Font.ColorIndex = 3 Then countRed = countRed + 1 Next cell End Function |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to count cells colored red?
Thanks.
----- original message ----- "FSt1" wrote in message ... hi i know of no built in functions that will do colors so you UDF is about the only way. you might want to look at chip's site. he has a number of color functions to count, sum, sort, other. http://cpearson.com/excel/colors.htm Regards FSt1 "JoeU2004" wrote: I believe this question has been asked and answered before, but sigh, I did not pay attention at the time, and it is hard to find the threads now. Using an Excel formula and no VBA, how can I count the cells that are colored red due to font formatting (e.g, using Font.ColorIndex in VBA)? The CELL("color") function does not seem to give distinctive results in that case. (Only if negative values are colored, which is not my situation.) I do have an alternative: I can write a UDF to count them. But I wonder if there is any way to do this with Excel formulas alone. FYI, my UDF is below. Any suggestions for improvements would be welcomed. For example, I do not like hardcoding the color index 3 (red). Function countRed(rng As Range) As Double Dim cell As Range For Each cell In rng If cell.Font.ColorIndex = 3 Then countRed = countRed + 1 Next cell End Function |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to count cells colored red?
It's not impossible but it's also not exactly what you had in mind.
Using a helper column (can only test one cell at a time!) you can use the GET.CELL macro function and then call this through a worksheet formula. However, it suffers from the same problem as most other VBA methods for doing this in that a font color change does not trigger a calculation so the formulas won't update when the font color is changed. -- Biff Microsoft Excel MVP "JoeU2004" wrote in message ... Thanks. ----- original message ----- "FSt1" wrote in message ... hi i know of no built in functions that will do colors so you UDF is about the only way. you might want to look at chip's site. he has a number of color functions to count, sum, sort, other. http://cpearson.com/excel/colors.htm Regards FSt1 "JoeU2004" wrote: I believe this question has been asked and answered before, but sigh, I did not pay attention at the time, and it is hard to find the threads now. Using an Excel formula and no VBA, how can I count the cells that are colored red due to font formatting (e.g, using Font.ColorIndex in VBA)? The CELL("color") function does not seem to give distinctive results in that case. (Only if negative values are colored, which is not my situation.) I do have an alternative: I can write a UDF to count them. But I wonder if there is any way to do this with Excel formulas alone. FYI, my UDF is below. Any suggestions for improvements would be welcomed. For example, I do not like hardcoding the color index 3 (red). Function countRed(rng As Range) As Double Dim cell As Range For Each cell In rng If cell.Font.ColorIndex = 3 Then countRed = countRed + 1 Next cell End Function |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do you count colored cells? | Excel Worksheet Functions | |||
count colored cells in excel | Excel Worksheet Functions | |||
count colored cells? | Excel Worksheet Functions | |||
Count non-colored cells | Excel Discussion (Misc queries) | |||
Count or sum colored cells | Excel Worksheet Functions |