|
Conditional formatting and counting.
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. |
Conditional formatting and counting.
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 he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html Pank wrote: 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 |
Conditional formatting and counting.
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" wrote: 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. |
Conditional formatting and counting.
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 dont understand what the 22 is at end of line that starts formula? Where/what cells will the answers be stored in? "Joel" wrote: 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" wrote: 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. |
Conditional formatting and counting.
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" wrote: 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 dont understand what the 22 is at end of line that starts formula? Where/what cells will the answers be stored in? "Joel" wrote: 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" wrote: 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. |
Conditional formatting and counting.
Joel,
Thank you for your assistance, muchly appreciated. "Joel" wrote: 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" wrote: 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 dont understand what the 22 is at end of line that starts formula? Where/what cells will the answers be stored in? "Joel" wrote: 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" wrote: 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. |
| All times are GMT +1. The time now is 07:02 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com