Conditional formatting and then counting specifics within it.

[Guest]

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.

 

[Bernard Liengme]

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

Suppose Format 1 is AND(AN1>date(2003,1,1),AP1<date(2003,10,1)
The to count the number of cells that satisfy these conditions
=SUMPRODUCT(--(AN1:AN100>date(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

 

[Guest]

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.