Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
a macro for conditional sum based on cell color index
My question: How do I use conditional formatting in Excel to sum highlighted
cells? Answer: I find the following macro in the internet: source: http://answers.google.com/answers/th.../id/67275.html However it doesn't work and gives #value error. What is the problem with this user-defined function? If you have a better solution, let me know. Function CFmt(RangeInQuotes, ColorIndex) Dim Total As Double Set Acell = Range(RangeInQuotes) 'Loop each cell in the range and if cell background eq to color index sum it For Each cell In Acell If cell.Interior.ColorIndex = ColorIndex Then Total = Total + cell.Value End If Next CFmt = Total End Function |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
a macro for conditional sum based on cell color index
I don't see where Conditional Formatting comes in, but you can sum
colored cells with the following function: Function SumByColor(RR As Range, ColorIndex As Integer, _ Optional OfText As Boolean = False) As Variant Dim R As Range Dim Total As Double If ColorIndex < 1 Or ColorIndex 56 Then SumByColor = CVErr(xlErrValue) Exit Function End If On Error GoTo ErrH: For Each R In RR.Cells If OfText = True Then If R.Font.ColorIndex = ColorIndex Then Total = Total + R.Value End If Else If R.Interior.ColorIndex = ColorIndex Then Total = Total + R.Value End If End If Next R SumByColor = Total Exit Function ErrH: SumByColor = CVErr(xlErrNum) End Function Here, you pass in the range of cells to test, the ColorIndex (1 <= ColorIndex <= 56), and TRUE or FALSE indicating whether to text the color of the font (TRUE) or of the background fill (FALSE or omitted). With this function, you can sum the cells in A1:A10 that have red backgrounds with =SumByColor(A1:A10,3,FALSE) to sum by font color, use =SumByColor(A1:A10,3,TRUE) The functions returns #VALUE if ColorIndex is invalid, or #NUM if a non-numeric value is found. For lots more about working with colors in Excel, see http://www.cpearson.com/excel/colors.aspx Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Sun, 25 Jan 2009 13:26:02 -0800, Khoshravan wrote: My question: How do I use conditional formatting in Excel to sum highlighted cells? Answer: I find the following macro in the internet: source: http://answers.google.com/answers/th.../id/67275.html However it doesn't work and gives #value error. What is the problem with this user-defined function? If you have a better solution, let me know. Function CFmt(RangeInQuotes, ColorIndex) Dim Total As Double Set Acell = Range(RangeInQuotes) 'Loop each cell in the range and if cell background eq to color index sum it For Each cell In Acell If cell.Interior.ColorIndex = ColorIndex Then Total = Total + cell.Value End If Next CFmt = Total End Function |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
a macro for conditional sum based on cell color index
Hi,
The color and colorindex of a cell are not the same as the colour of a cell arrived at by conditional formatting and summing by the latter isn't easy. The best method I know of is on the xldynamic site but tonight the site is down and you may choose to look tomorrow http://xldynamic.com/source/xld.ColourCounter.html Chip pearson has some methods that i've never used but knowing the stuff he produces I doubt you'll go far wrong there. http://www.cpearson.com/excel/CFColors.htm Mike "Khoshravan" wrote: My question: How do I use conditional formatting in Excel to sum highlighted cells? Answer: I find the following macro in the internet: source: http://answers.google.com/answers/th.../id/67275.html However it doesn't work and gives #value error. What is the problem with this user-defined function? If you have a better solution, let me know. Function CFmt(RangeInQuotes, ColorIndex) Dim Total As Double Set Acell = Range(RangeInQuotes) 'Loop each cell in the range and if cell background eq to color index sum it For Each cell In Acell If cell.Interior.ColorIndex = ColorIndex Then Total = Total + cell.Value End If Next CFmt = Total End Function |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
a macro for conditional sum based on cell color index
Dear Chip Pearson
Thanks for your reply. I always enjoy your solutions. I am happy to see that you are answering to my question. Your function worked very well. thanks for that. In the mean time could you please debug the sent Function, as I want to master my programming skills as well. PS) Yes what I want to do, has nothing to do with conditional formatting. If I have mentioned it, I am wrong. "Chip Pearson" wrote: I don't see where Conditional Formatting comes in, but you can sum colored cells with the following function: Function SumByColor(RR As Range, ColorIndex As Integer, _ Optional OfText As Boolean = False) As Variant Dim R As Range Dim Total As Double If ColorIndex < 1 Or ColorIndex 56 Then SumByColor = CVErr(xlErrValue) Exit Function End If On Error GoTo ErrH: For Each R In RR.Cells If OfText = True Then If R.Font.ColorIndex = ColorIndex Then Total = Total + R.Value End If Else If R.Interior.ColorIndex = ColorIndex Then Total = Total + R.Value End If End If Next R SumByColor = Total Exit Function ErrH: SumByColor = CVErr(xlErrNum) End Function Here, you pass in the range of cells to test, the ColorIndex (1 <= ColorIndex <= 56), and TRUE or FALSE indicating whether to text the color of the font (TRUE) or of the background fill (FALSE or omitted). With this function, you can sum the cells in A1:A10 that have red backgrounds with =SumByColor(A1:A10,3,FALSE) to sum by font color, use =SumByColor(A1:A10,3,TRUE) The functions returns #VALUE if ColorIndex is invalid, or #NUM if a non-numeric value is found. For lots more about working with colors in Excel, see http://www.cpearson.com/excel/colors.aspx Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Sun, 25 Jan 2009 13:26:02 -0800, Khoshravan wrote: My question: How do I use conditional formatting in Excel to sum highlighted cells? Answer: I find the following macro in the internet: source: http://answers.google.com/answers/th.../id/67275.html However it doesn't work and gives #value error. What is the problem with this user-defined function? If you have a better solution, let me know. Function CFmt(RangeInQuotes, ColorIndex) Dim Total As Double Set Acell = Range(RangeInQuotes) 'Loop each cell in the range and if cell background eq to color index sum it For Each cell In Acell If cell.Interior.ColorIndex = ColorIndex Then Total = Total + cell.Value End If Next CFmt = Total End Function |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
a macro for conditional sum based on cell color index
Your function worked very well. thanks for that. In the mean time could you please debug the sent Function, Your original function is more or less correct, with some minor errors. A few things.... First, not all the variables are declared. While this is legal, it is very bad programming practice, and a habit of which you should disabuse yourself as soon as possible. See http://www.cpearson.com/excel/DeclaringVariables.aspx for a discussion of properly declaring variables, including the use of Option Explicit. Next, the function requires that the range to test be enclosed in quotes. That is, =cfmt("A1:A15",3) instead of =cfmt(A1:A15,3) If you omit the quotes, you'll get a #VALUE error. While one could debate the merits of either approach, I would not use quotes and pass an actual range, not a string representing a range (as in the second syntax above). Finally, the code does not check to make sure that the value being added to Total is numeric. A non-numeric value would cause a #VALUE error due to a type mismatch (error 13) error. A corrected version of your function is shown below: Function CFmt(RangeInQuotes, ColorIndex) Dim ACell As Range Dim Total As Double Dim Cell As Range Set ACell = Range(RangeInQuotes) For Each Cell In ACell If Cell.Interior.ColorIndex = ColorIndex Then If IsNumeric(Cell.Value) = True Then Total = Total + Cell.Value End If End If Next CFmt = Total End Function Call this from a cell with =CFmt("A1:A15",3) to sum elements in A1:A15 that have a red background. See ColorIndex in VBA Help for a list of colors and their ColorIndex values. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Sun, 25 Jan 2009 14:06:05 -0800, Khoshravan wrote: Dear Chip Pearson Thanks for your reply. I always enjoy your solutions. I am happy to see that you are answering to my question. Your function worked very well. thanks for that. In the mean time could you please debug the sent Function, as I want to master my programming skills as well. PS) Yes what I want to do, has nothing to do with conditional formatting. If I have mentioned it, I am wrong. "Chip Pearson" wrote: I don't see where Conditional Formatting comes in, but you can sum colored cells with the following function: Function SumByColor(RR As Range, ColorIndex As Integer, _ Optional OfText As Boolean = False) As Variant Dim R As Range Dim Total As Double If ColorIndex < 1 Or ColorIndex 56 Then SumByColor = CVErr(xlErrValue) Exit Function End If On Error GoTo ErrH: For Each R In RR.Cells If OfText = True Then If R.Font.ColorIndex = ColorIndex Then Total = Total + R.Value End If Else If R.Interior.ColorIndex = ColorIndex Then Total = Total + R.Value End If End If Next R SumByColor = Total Exit Function ErrH: SumByColor = CVErr(xlErrNum) End Function Here, you pass in the range of cells to test, the ColorIndex (1 <= ColorIndex <= 56), and TRUE or FALSE indicating whether to text the color of the font (TRUE) or of the background fill (FALSE or omitted). With this function, you can sum the cells in A1:A10 that have red backgrounds with =SumByColor(A1:A10,3,FALSE) to sum by font color, use =SumByColor(A1:A10,3,TRUE) The functions returns #VALUE if ColorIndex is invalid, or #NUM if a non-numeric value is found. For lots more about working with colors in Excel, see http://www.cpearson.com/excel/colors.aspx Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Sun, 25 Jan 2009 13:26:02 -0800, Khoshravan wrote: My question: How do I use conditional formatting in Excel to sum highlighted cells? Answer: I find the following macro in the internet: source: http://answers.google.com/answers/th.../id/67275.html However it doesn't work and gives #value error. What is the problem with this user-defined function? If you have a better solution, let me know. Function CFmt(RangeInQuotes, ColorIndex) Dim Total As Double Set Acell = Range(RangeInQuotes) 'Loop each cell in the range and if cell background eq to color index sum it For Each cell In Acell If cell.Interior.ColorIndex = ColorIndex Then Total = Total + cell.Value End If Next CFmt = Total End Function |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
a macro for conditional sum based on cell color index
Dear Chip
Thanks so much for putting your valuable time and energy to reply to my question. Sorry for my poor programming skills and for asking such basic questions. I should have checked with VBA help before asking. "Chip Pearson" wrote: Your function worked very well. thanks for that. In the mean time could you please debug the sent Function, Your original function is more or less correct, with some minor errors. A few things.... First, not all the variables are declared. While this is legal, it is very bad programming practice, and a habit of which you should disabuse yourself as soon as possible. See http://www.cpearson.com/excel/DeclaringVariables.aspx for a discussion of properly declaring variables, including the use of Option Explicit. Next, the function requires that the range to test be enclosed in quotes. That is, =cfmt("A1:A15",3) instead of =cfmt(A1:A15,3) If you omit the quotes, you'll get a #VALUE error. While one could debate the merits of either approach, I would not use quotes and pass an actual range, not a string representing a range (as in the second syntax above). Finally, the code does not check to make sure that the value being added to Total is numeric. A non-numeric value would cause a #VALUE error due to a type mismatch (error 13) error. A corrected version of your function is shown below: Function CFmt(RangeInQuotes, ColorIndex) Dim ACell As Range Dim Total As Double Dim Cell As Range Set ACell = Range(RangeInQuotes) For Each Cell In ACell If Cell.Interior.ColorIndex = ColorIndex Then If IsNumeric(Cell.Value) = True Then Total = Total + Cell.Value End If End If Next CFmt = Total End Function Call this from a cell with =CFmt("A1:A15",3) to sum elements in A1:A15 that have a red background. See ColorIndex in VBA Help for a list of colors and their ColorIndex values. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Sun, 25 Jan 2009 14:06:05 -0800, Khoshravan wrote: Dear Chip Pearson Thanks for your reply. I always enjoy your solutions. I am happy to see that you are answering to my question. Your function worked very well. thanks for that. In the mean time could you please debug the sent Function, as I want to master my programming skills as well. PS) Yes what I want to do, has nothing to do with conditional formatting. If I have mentioned it, I am wrong. "Chip Pearson" wrote: I don't see where Conditional Formatting comes in, but you can sum colored cells with the following function: Function SumByColor(RR As Range, ColorIndex As Integer, _ Optional OfText As Boolean = False) As Variant Dim R As Range Dim Total As Double If ColorIndex < 1 Or ColorIndex 56 Then SumByColor = CVErr(xlErrValue) Exit Function End If On Error GoTo ErrH: For Each R In RR.Cells If OfText = True Then If R.Font.ColorIndex = ColorIndex Then Total = Total + R.Value End If Else If R.Interior.ColorIndex = ColorIndex Then Total = Total + R.Value End If End If Next R SumByColor = Total Exit Function ErrH: SumByColor = CVErr(xlErrNum) End Function Here, you pass in the range of cells to test, the ColorIndex (1 <= ColorIndex <= 56), and TRUE or FALSE indicating whether to text the color of the font (TRUE) or of the background fill (FALSE or omitted). With this function, you can sum the cells in A1:A10 that have red backgrounds with =SumByColor(A1:A10,3,FALSE) to sum by font color, use =SumByColor(A1:A10,3,TRUE) The functions returns #VALUE if ColorIndex is invalid, or #NUM if a non-numeric value is found. For lots more about working with colors in Excel, see http://www.cpearson.com/excel/colors.aspx Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Sun, 25 Jan 2009 13:26:02 -0800, Khoshravan wrote: My question: How do I use conditional formatting in Excel to sum highlighted cells? Answer: I find the following macro in the internet: source: http://answers.google.com/answers/th.../id/67275.html However it doesn't work and gives #value error. What is the problem with this user-defined function? If you have a better solution, let me know. Function CFmt(RangeInQuotes, ColorIndex) Dim Total As Double Set Acell = Range(RangeInQuotes) 'Loop each cell in the range and if cell background eq to color index sum it For Each cell In Acell If cell.Interior.ColorIndex = ColorIndex Then Total = Total + cell.Value End If Next CFmt = Total End Function |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
a macro for conditional sum based on cell color index
The site you mentioned, is not available. You told it will be down for a day
but now it is more than a day. "Mike H" wrote: Hi, The color and colorindex of a cell are not the same as the colour of a cell arrived at by conditional formatting and summing by the latter isn't easy. The best method I know of is on the xldynamic site but tonight the site is down and you may choose to look tomorrow http://xldynamic.com/source/xld.ColourCounter.html Chip pearson has some methods that i've never used but knowing the stuff he produces I doubt you'll go far wrong there. http://www.cpearson.com/excel/CFColors.htm Mike "Khoshravan" wrote: My question: How do I use conditional formatting in Excel to sum highlighted cells? Answer: I find the following macro in the internet: source: http://answers.google.com/answers/th.../id/67275.html However it doesn't work and gives #value error. What is the problem with this user-defined function? If you have a better solution, let me know. Function CFmt(RangeInQuotes, ColorIndex) Dim Total As Double Set Acell = Range(RangeInQuotes) 'Loop each cell in the range and if cell background eq to color index sum it For Each cell In Acell If cell.Interior.ColorIndex = ColorIndex Then Total = Total + cell.Value End If Next CFmt = Total End Function |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Sum Based on Cell Background Color | Excel Worksheet Functions | |||
Conditional Formatting based in cell color | Excel Discussion (Misc queries) | |||
Conditional format a cell based on color of another | Excel Discussion (Misc queries) | |||
Conditional Formatting - Color row based on one cell value | Excel Programming | |||
Conditional Formatted Cell Color Index | Excel Programming |