Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting cells that are shaded in black?
How can I use the color of a cell in a formula? I need to tally all the cells
in one column that are black. They are black as the result of another formula but I want to give a count in a summary page. Thanks in advance! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting cells that are shaded in black?
They are black as the result of another formula
but I want to give a count in a summary page. Use the logic of *why* the cells are black to build a formula to count them. You say they're black as the result of another formula....so what is the result of that other formula that causes the cells to turn black? Base your count on that logic. For example, the cells turn black when they contain a number greater than 100. Then: =COUNTIF(A1:A10,"100") -- Biff Microsoft Excel MVP "Denise" wrote in message ... How can I use the color of a cell in a formula? I need to tally all the cells in one column that are black. They are black as the result of another formula but I want to give a count in a summary page. Thanks in advance! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting cells that are shaded in black?
The formula that turns the cells black is too complicated for me to add more
code too. It takes one column and checks to see if there is an 'x' in it and then uses the Date variable to see if the date in the another column is more than 30-days old. If it is then the cell in the date column turns black and the font turns white. {using the conditional formatting feature} Formula is =AND(TODAY()-30$C2,$D2<"x") <Choose my Format C2 holds the date and D2 holds either an "x" or it's blank. I am trying to count the cells in C that are formatted Black because #1. The Cell D2 is empty (incomplete). AND #2. They have been incomplete more than 30-days. I have multiple sheets and I need this formula to check each one for the same data that will be summarized on a separate sheet and I am very rusty. My multiple attempts at logic failed. Thank you anyway. o" wrote: They are black as the result of another formula but I want to give a count in a summary page. Use the logic of *why* the cells are black to build a formula to count them. You say they're black as the result of another formula....so what is the result of that other formula that causes the cells to turn black? Base your count on that logic. For example, the cells turn black when they contain a number greater than 100. Then: =COUNTIF(A1:A10,"100") -- Biff Microsoft Excel MVP "Denise" wrote in message ... How can I use the color of a cell in a formula? I need to tally all the cells in one column that are black. They are black as the result of another formula but I want to give a count in a summary page. Thanks in advance! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting cells that are shaded in black?
Let's assume the range of cells to count is C2:C100
=SUMPRODUCT(--(TODAY()-30C2:C100),--(D2:D100<"x")) -- Biff Microsoft Excel MVP "Denise" wrote in message ... The formula that turns the cells black is too complicated for me to add more code too. It takes one column and checks to see if there is an 'x' in it and then uses the Date variable to see if the date in the another column is more than 30-days old. If it is then the cell in the date column turns black and the font turns white. {using the conditional formatting feature} Formula is =AND(TODAY()-30$C2,$D2<"x") <Choose my Format C2 holds the date and D2 holds either an "x" or it's blank. I am trying to count the cells in C that are formatted Black because #1. The Cell D2 is empty (incomplete). AND #2. They have been incomplete more than 30-days. I have multiple sheets and I need this formula to check each one for the same data that will be summarized on a separate sheet and I am very rusty. My multiple attempts at logic failed. Thank you anyway. o" wrote: They are black as the result of another formula but I want to give a count in a summary page. Use the logic of *why* the cells are black to build a formula to count them. You say they're black as the result of another formula....so what is the result of that other formula that causes the cells to turn black? Base your count on that logic. For example, the cells turn black when they contain a number greater than 100. Then: =COUNTIF(A1:A10,"100") -- Biff Microsoft Excel MVP "Denise" wrote in message ... How can I use the color of a cell in a formula? I need to tally all the cells in one column that are black. They are black as the result of another formula but I want to give a count in a summary page. Thanks in advance! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting cells that are shaded in black?
Hi,
Here is a custom function to count cells base on font color and fill color: Function CountFormats(R As Range, E As Range) As Integer Dim cell As Range Dim Total As Integer Application.Volatile Set S = E.Cells(1, 1) Total = 0 For Each cell In R With cell If .Interior.ColorIndex = S.Interior.ColorIndex _ And .Font.ColorIndex = S.Font.ColorIndex Then Total = Total + 1 End If End With Next cell CountFormats = Total End Function In the spreadsheet you enter =countformats(A2:B7,D2) Where A2:B7 is the range you want to check and D2 is a cell formatted to the desired format. To add this code to a workbook press Alt+F11 and select your file in the Project explorer in the top left side of the screen. Choose Insert, Module. Put the code in the resulting module. If this helps, please click the Yes button Cheers, Shane Devenshire "Denise" wrote: The formula that turns the cells black is too complicated for me to add more code too. It takes one column and checks to see if there is an 'x' in it and then uses the Date variable to see if the date in the another column is more than 30-days old. If it is then the cell in the date column turns black and the font turns white. {using the conditional formatting feature} Formula is =AND(TODAY()-30$C2,$D2<"x") <Choose my Format C2 holds the date and D2 holds either an "x" or it's blank. I am trying to count the cells in C that are formatted Black because #1. The Cell D2 is empty (incomplete). AND #2. They have been incomplete more than 30-days. I have multiple sheets and I need this formula to check each one for the same data that will be summarized on a separate sheet and I am very rusty. My multiple attempts at logic failed. Thank you anyway. o" wrote: They are black as the result of another formula but I want to give a count in a summary page. Use the logic of *why* the cells are black to build a formula to count them. You say they're black as the result of another formula....so what is the result of that other formula that causes the cells to turn black? Base your count on that logic. For example, the cells turn black when they contain a number greater than 100. Then: =COUNTIF(A1:A10,"100") -- Biff Microsoft Excel MVP "Denise" wrote in message ... How can I use the color of a cell in a formula? I need to tally all the cells in one column that are black. They are black as the result of another formula but I want to give a count in a summary page. Thanks in advance! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting cells that are shaded in black?
That won't work on cells that are conditionally formatted as is the case
with the OP. -- Biff Microsoft Excel MVP "Shane Devenshire" wrote in message ... Hi, Here is a custom function to count cells base on font color and fill color: Function CountFormats(R As Range, E As Range) As Integer Dim cell As Range Dim Total As Integer Application.Volatile Set S = E.Cells(1, 1) Total = 0 For Each cell In R With cell If .Interior.ColorIndex = S.Interior.ColorIndex _ And .Font.ColorIndex = S.Font.ColorIndex Then Total = Total + 1 End If End With Next cell CountFormats = Total End Function In the spreadsheet you enter =countformats(A2:B7,D2) Where A2:B7 is the range you want to check and D2 is a cell formatted to the desired format. To add this code to a workbook press Alt+F11 and select your file in the Project explorer in the top left side of the screen. Choose Insert, Module. Put the code in the resulting module. If this helps, please click the Yes button Cheers, Shane Devenshire "Denise" wrote: The formula that turns the cells black is too complicated for me to add more code too. It takes one column and checks to see if there is an 'x' in it and then uses the Date variable to see if the date in the another column is more than 30-days old. If it is then the cell in the date column turns black and the font turns white. {using the conditional formatting feature} Formula is =AND(TODAY()-30$C2,$D2<"x") <Choose my Format C2 holds the date and D2 holds either an "x" or it's blank. I am trying to count the cells in C that are formatted Black because #1. The Cell D2 is empty (incomplete). AND #2. They have been incomplete more than 30-days. I have multiple sheets and I need this formula to check each one for the same data that will be summarized on a separate sheet and I am very rusty. My multiple attempts at logic failed. Thank you anyway. o" wrote: They are black as the result of another formula but I want to give a count in a summary page. Use the logic of *why* the cells are black to build a formula to count them. You say they're black as the result of another formula....so what is the result of that other formula that causes the cells to turn black? Base your count on that logic. For example, the cells turn black when they contain a number greater than 100. Then: =COUNTIF(A1:A10,"100") -- Biff Microsoft Excel MVP "Denise" wrote in message ... How can I use the color of a cell in a formula? I need to tally all the cells in one column that are black. They are black as the result of another formula but I want to give a count in a summary page. Thanks in advance! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Shaded cells won't print shaded | Excel Worksheet Functions | |||
In Excel, colored text is printing in black. Shaded cells are ok? | Excel Discussion (Misc queries) | |||
linking shaded cells | Excel Discussion (Misc queries) | |||
Not printing shaded cells... | Excel Discussion (Misc queries) | |||
Counting shaded cells | Excel Worksheet Functions |