Detect if conditional format is true

B

Bill

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
 
L

LeninVMS

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
 
B

Bob Phillips

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)
 
B

Bob Phillips

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)
 
B

Bill

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
 
B

Bob Phillips

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)
 
Top