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!

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!

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!

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!

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!

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!