Hi people,

I have a worksheet with lots of different conditional
formats in different cells.

I want to be able to count all the red(or yellow etc)
cells on my worksheet.

However, this may mean cells that I have formatted
manually, which I know I can count using a test on cells
(x,y).interior.colorindex to increment a variable; or it
could mean cells that have been coloured in red by the
conditional format (using condition 1, 2 or 3, and a
different unrelated formula for each cell).

Hope you can help me,

Bill J. Duke

You can use the FormatConditions property of the Range object t
distinguish between cells that have conditional formats and those tha
do not.

E.g:

Count all red cells using cells(x,y).interior.colorindex

If (Cells(i,j).FormatConditions.Count<0) Then
'Identify color of cell and decrease count from total number o
red cells

End if

- Leni

--
Message posted from http://www.ExcelForum.com

Bill,

Test the cells to see if they match the CF criteria. For instance if the CF
colour if a cell 5 then

=COUNTIF(A1:A1000,"5") will give the count of those coloured cells.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Bill" wrote in message
...
Hi people,

I have a worksheet with lots of different conditional
formats in different cells.

I want to be able to count all the red(or yellow etc)
cells on my worksheet.

However, this may mean cells that I have formatted
manually, which I know I can count using a test on cells
(x,y).interior.colorindex to increment a variable; or it
could mean cells that have been coloured in red by the
conditional format (using condition 1, 2 or 3, and a
different unrelated formula for each cell).

Hope you can help me,

Bill J. Duke

Be warned, This can get very complex and has a few very nasty curlies, I
know, I have done it.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"LeninVMS " wrote in message
...
You can use the FormatConditions property of the Range object to
distinguish between cells that have conditional formats and those that
do not.

E.g:

Count all red cells using cells(x,y).interior.colorindex

If (Cells(i,j).FormatConditions.Count<0) Then
'Identify color of cell and decrease count from total number of
red cells

End if

- Lenin


---
Message posted from http://www.ExcelForum.com/

Bob,

The problem is that I have a whole lot of different
conditions on the different cells - just the colour output
is the thing I need to test.

Bill

-----Original Message-----
Bill,

Test the cells to see if they match the CF criteria. For
instance if the CF
colour if a cell 5 then

=COUNTIF(A1:A1000,"5") will give the count of those
coloured cells.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Bill" wrote in message
...
Hi people,

I have a worksheet with lots of different conditional
formats in different cells.

I want to be able to count all the red(or yellow etc)
cells on my worksheet.

However, this may mean cells that I have formatted
manually, which I know I can count using a test on cells
(x,y).interior.colorindex to increment a variable; or it
could mean cells that have been coloured in red by the
conditional format (using condition 1, 2 or 3, and a
different unrelated formula for each cell).

Hope you can help me,

Bill J. Duke


.

Bill,

You cannot use the same code to test for manual cell colour and for CF cell
colour, so you immediately need two solutions. And CD cell colouring is, as
I said, not a trivial piece of code. As any colour in CF must be based upon
a criteria, testing a range for that same criteria is relatively simple.

SO if you wrote a special function to count the manual colours, then the
total count would be something like

=ColourCount(A1:A1000) + COUNTIF(A1:A1000,"5") +
SUMPRODUCT(--(MONTH(A1:A1000)=12))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Bill" wrote in message
...
Bob,

The problem is that I have a whole lot of different
conditions on the different cells - just the colour output
is the thing I need to test.

Bill

-----Original Message-----
Bill,

Test the cells to see if they match the CF criteria. For
instance if the CF
colour if a cell 5 then

=COUNTIF(A1:A1000,"5") will give the count of those
coloured cells.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Bill" wrote in message
...
Hi people,

I have a worksheet with lots of different conditional
formats in different cells.

I want to be able to count all the red(or yellow etc)
cells on my worksheet.

However, this may mean cells that I have formatted
manually, which I know I can count using a test on cells
(x,y).interior.colorindex to increment a variable; or it
could mean cells that have been coloured in red by the
conditional format (using condition 1, 2 or 3, and a
different unrelated formula for each cell).

Hope you can help me,

Bill J. Duke


.