Conditional formatting and counting.

[Pank]

I have the following Conditional formatting set up in a macro (which works as
expected):-

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


Please note, Columns F, AM and AN contain dates in the format dd/mm/yyyy.

In Column G, I have number in the range 1 to 5.

Is there a way that for the above conditional formats, I can then count the
number of 1,2,3,4,5 that appear in column G for the individual conditional
format?

So if my data looks like:-

Col F Col G Col AM Col AN
21/12/2003 5 01/01/2003 31/03/2003
27/11/2003 5 01/04/2003 31/08/2003
19/11/2003 4 01/09/2003 31/12/2003
16/11/2003 5
25/09/2003 5
28/08/2003 4
14/08/2003 5
15/07/2003 5
04/05/2003 5

Therefore for a data range AM3 AN3 I would expect to see 5*4, 4*1, 3*0, 2*0
1*0 (i.e. Data from the first 5 records).

Any help would be appreciated.

 

[Dave Peterson]

Working with the conditional formatting colors is not an easy task.

But you could use the same rules in formulas that would do the counting:

=sumproduct(--($f$1:$f$999>=$am$2),
--($f$1:$f$999<=$an$2),
--($g$1:$g$999=5))

(or put that 5 in a cell and refer to that cell in the formula)

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

 

[Joel]

You need something like below code. Change conditional formating to an
expression rather than cell is. You can't add a line condition in the middle
of a string so I had to break up the string into Fa + FB + FC + FD + FE. It
wassn't clear from your posting the range of the SUMPRODUCT that you were
looking for.

Sub xyz()

FA = "5*countif(AG2:AG6,""=5""),"
FB = "4*countif(AG2:AG6,""=4""),"
FC = "3*countif(AG2:AG6,""=3""),"
FD = "2*countif(AG2:AG6,""=2""),"
FE = "1*countif(AG2:AG6,""=1"")"

Columns("F:F").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, _
Formula1:="=if(SUMPRODUCT(FA+FB+FC+FD+FE)=22,true,false)"
Selection.FormatConditions(1).Interior.ColorIndex = 38



End Sub

 

[Pank]

Dave, Joel,

Firstly, many thanks for your prompt response and help with my question.

Dave, I wanted to insert the code in a macro as the process is run in over a
100 sheets.

Having inserted in a macro, I get errors along the line ‘ = Expected: Line
no ….â€, If I remove the =, I get “Invalid character and it highlights the
$f$1:$f$999 in Red.

I copied your code into a couple of sheets, and as expected it worked a treat.

Therefore what changes are required to insert your code within a macro?

Joel, The range that has the values if F1 to F999. Therefore I am assuming
that I replace your reference to AG2:AG6 with F1:F999?. Secondly, I don’t
understand what the 22 is at end of line that starts formula?

Where/what cells will the answers be stored in?

 

[Joel]

The sumproduct need tto have a range. If sumproduct is < 22 then color red,

=22 color blue or something like this. You didn't specify a limit so I

picked 22. You just said you wanted conditional formatting based on the
sumproduct. limits.

the range can be any range of cells you need.

 

[Pank]

Joel,

Thank you for your assistance, muchly appreciated.