Conditional formatting and then counting specifics within it.
 

I have a work book that can have up to 100 sheets within it.

An example of a sheet is:-

Col B Col F Col N Col AM Col AN Col AO Col AP
Test 1 12/03/03 1 01/01/03 31/03/03 01/01/02 31/03/02
Test 2 01/01/03 5 01/04/03 31/08/03 01/04/02 31/08/02
Test 3 01/09/03 2 01/09/03 31/12/03 01/09/02 31/12/02
Test 4 12/12/03 2
Test 5 14/04/03 3
Test 6 12/03/03 1

Column B hold a name.
Column F holds a date of birth.
Column N hold a number in the range 1 to 5.
Columns AM to AP are start and end dates for current year and previous year
for specific periods in the format dd/mm/yy.

I have used 3 Conditional formatting statements in column F to basically
colour code it by using dates in specified in columns Am to AP.

What I need is to count the items reported by Conditional Format 1, and then
count the total number of 1 to 5 that are present in that range.

I then need the same reported by Conditional Format 2 and 3.

Therefore for the above data I would expect conditional format 1 to
highlight 3 records (Test 1, Test 2 and Test 6) with the following counts
Range 1;2 Range 2;0 Range 3;0 Range 4;0 Range 5;1.

Conditional format 2 would highlight 1 records (Test 5) with the following
counts Range 1;0 Range 2;0 Range 3;1 Range 4;0 Range 5;0.

Conditional format 3 would highlight 2 records (Test 3, Test 4) with the
following counts Range 1;0 Range 2;2 Range 3;0 Range 4;0 Range 5;0.

What ever is required to achieve the above will be inserted in a macro that
will be run against all sheets in the workbook.

Any assistance offered would be appreciated.

Conditional formatting and then counting specifics within it.
 

Any formula you use in Conditional Formatting can be as a "condition" in
SUMIF, SUMPRODUCT, etc.

Suppose Format 1 is AND(AN1date(2003,1,1),AP1<date(2003,10,1)
The to count the number of cells that satisfy these conditions
=SUMPRODUCT(--(AN1:AN100date(2003,1,1),--(AP1:AP100<date(2003,10,1))
see
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

http://mcgimpsey.com/excel/formulae/doubleneg.html

If you tell us more about the CF formulas we can be more specific
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Pank" wrote in message
...
I have a work book that can have up to 100 sheets within it.

An example of a sheet is:-

Col B Col F Col N Col AM Col AN Col AO Col AP
Test 1 12/03/03 1 01/01/03 31/03/03 01/01/02 31/03/02
Test 2 01/01/03 5 01/04/03 31/08/03 01/04/02 31/08/02
Test 3 01/09/03 2 01/09/03 31/12/03 01/09/02 31/12/02
Test 4 12/12/03 2
Test 5 14/04/03 3
Test 6 12/03/03 1

Column B hold a name.
Column F holds a date of birth.
Column N hold a number in the range 1 to 5.
Columns AM to AP are start and end dates for current year and previous
year
for specific periods in the format dd/mm/yy.

I have used 3 Conditional formatting statements in column F to basically
colour code it by using dates in specified in columns Am to AP.

What I need is to count the items reported by Conditional Format 1, and
then
count the total number of 1 to 5 that are present in that range.

I then need the same reported by Conditional Format 2 and 3.

Therefore for the above data I would expect conditional format 1 to
highlight 3 records (Test 1, Test 2 and Test 6) with the following counts
Range 1;2 Range 2;0 Range 3;0 Range 4;0 Range 5;1.

Conditional format 2 would highlight 1 records (Test 5) with the following
counts Range 1;0 Range 2;0 Range 3;1 Range 4;0 Range 5;0.

Conditional format 3 would highlight 2 records (Test 3, Test 4) with the
following counts Range 1;0 Range 2;2 Range 3;0 Range 4;0 Range
5;0.

What ever is required to achieve the above will be inserted in a macro
that
will be run against all sheets in the workbook.

Any assistance offered would be appreciated.

Conditional formatting and then counting specifics within it.
 

Bernard,

Thank you for your assistance. The conditional statements I have that runs
as part of a macro is :-

Columns("F:F").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
Formula1:="=$AM$2", Formula2:="=$AN$2"
Selection.FormatConditions(1).Interior.ColorIndex = 38
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
Formula1:="=$AM$3", Formula2:="=$AN$3"
Selection.FormatConditions(2).Interior.ColorIndex = 40
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
Formula1:="=$AM$4", Formula2:="=$AN$4"
Selection.FormatConditions(3).Interior.ColorIndex = 36

Any further assistance you can offer would be appreciated.




"Bernard Liengme" wrote:

Any formula you use in Conditional Formatting can be as a "condition" in
SUMIF, SUMPRODUCT, etc.

Suppose Format 1 is AND(AN1date(2003,1,1),AP1<date(2003,10,1)
The to count the number of cells that satisfy these conditions
=SUMPRODUCT(--(AN1:AN100date(2003,1,1),--(AP1:AP100<date(2003,10,1))
see
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

http://mcgimpsey.com/excel/formulae/doubleneg.html

If you tell us more about the CF formulas we can be more specific
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Pank" wrote in message
...
I have a work book that can have up to 100 sheets within it.

An example of a sheet is:-

Col B Col F Col N Col AM Col AN Col AO Col AP
Test 1 12/03/03 1 01/01/03 31/03/03 01/01/02 31/03/02
Test 2 01/01/03 5 01/04/03 31/08/03 01/04/02 31/08/02
Test 3 01/09/03 2 01/09/03 31/12/03 01/09/02 31/12/02
Test 4 12/12/03 2
Test 5 14/04/03 3
Test 6 12/03/03 1

Column B hold a name.
Column F holds a date of birth.
Column N hold a number in the range 1 to 5.
Columns AM to AP are start and end dates for current year and previous
year
for specific periods in the format dd/mm/yy.

I have used 3 Conditional formatting statements in column F to basically
colour code it by using dates in specified in columns Am to AP.

What I need is to count the items reported by Conditional Format 1, and
then
count the total number of 1 to 5 that are present in that range.

I then need the same reported by Conditional Format 2 and 3.

Therefore for the above data I would expect conditional format 1 to
highlight 3 records (Test 1, Test 2 and Test 6) with the following counts
Range 1;2 Range 2;0 Range 3;0 Range 4;0 Range 5;1.

Conditional format 2 would highlight 1 records (Test 5) with the following
counts Range 1;0 Range 2;0 Range 3;1 Range 4;0 Range 5;0.

Conditional format 3 would highlight 2 records (Test 3, Test 4) with the
following counts Range 1;0 Range 2;2 Range 3;0 Range 4;0 Range
5;0.

What ever is required to achieve the above will be inserted in a macro
that
will be run against all sheets in the workbook.

Any assistance offered would be appreciated.