Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Computing
Hi,
I am not sure Excel 2003 can do this or not. Here is what I would like to do. I have a column (say column A) with some numbers. Some of the cells are colored green and some are colored red. I would like to add all the cells colored in red at cell B1 and add all the cells colored in green at cell C1. The color may be changed dynamically by means of conditional formatting. Any idea? Thanks. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Computing
Hi,
You can do this in VBA of course (hint: use the worksheet_calculate event). But - before you go that rout, have you considered using the conditional sum wizard? It is designed to create conditional sum formulas using the same sorts of criteria as are used in conditional formatting. From your problem description, this seems like a good possibility. It is an add in. Go to tools - add ins and install it if it isn't already. This may be better than a VBA approach since, for example, the code might break if you decide you want to use different colors in the conditional formatting. It would probably also be quicker if you are talking about a large number of cells. HTH -John Coleman wrote: Hi, I am not sure Excel 2003 can do this or not. Here is what I would like to do. I have a column (say column A) with some numbers. Some of the cells are colored green and some are colored red. I would like to add all the cells colored in red at cell B1 and add all the cells colored in green at cell C1. The color may be changed dynamically by means of conditional formatting. Any idea? Thanks. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Computing
SUM them based upon the same condition that is applied to CF.
For instance, if the CF is greater than 10, then use =SUMIF(A1:A10,"10") -- --- HTH Bob (change the xxxx to gmail if mailing direct) wrote in message ups.com... Hi, I am not sure Excel 2003 can do this or not. Here is what I would like to do. I have a column (say column A) with some numbers. Some of the cells are colored green and some are colored red. I would like to add all the cells colored in red at cell B1 and add all the cells colored in green at cell C1. The color may be changed dynamically by means of conditional formatting. Any idea? Thanks. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Computing
I think John's probably right, especially if you are already using
Excel's built-in Conditional Formatting. However, should you decide to go the VBA route the following functions may be of some help. NB. These functions will NOT detect a cell interior colour that has been set using conditional formatting. To do that you would need to write code to interrogate the condtional format settings, in which case you might as well write your own conditional formatting routine (yeah, listen to John). Still reading? Okay go here for some Conditional Format macro suggestions... http://www.mvps.org/dmcritchie/excel/condfmt.htm Here's a function to do the summing... Public Function ColorSum(mRng As Range, mColor As Integer) As Single Dim mTot As Single Dim c As Range For Each c In mRng If IsNumeric(c.Value) Then If c.Interior.ColorIndex = mColor Then mTot = mTot + c.Value End If End If Next c ColorSum = mTot End Function To use enter something like =ColorSum(A1:A20,10) into a cell. And here's a function to return the interior colour index of a cell... Public Function GetColorIndex(mCell As Range) As Integer ''' Quick check to find the interior color of a cell. _ If multiple cells selected only top left examined GetColorIndex = mCell.Range("A1").Interior.ColorIndex End Function To use enter something like =GetColorIndex(A3) into a cell. Hope you listened to John - I'm just killing time. ?;^) NickH |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Computing
Bob,
Your advice is probably the way to go, but do you have any idea why the following doesn't work as intended? Function ColorSum(R As Range, i As Long) As Variant Dim sum As Variant Dim cl As Range For Each cl In R.Cells If cl.Interior.ColorIndex = i Then sum = sum + cl.Value End If Next cl ColorSum = sum End Function Sub RedGreenSums() Dim R As Range Set R = Range("A1", Cells(Rows.Count, 1).End(xlUp)) Range("B1").Value = ColorSum(R, 3) Range("C1").Value = ColorSum(R, 10) End Sub Private Sub Worksheet_Calculate() RedGreenSums End Sub 'For debugging purposes: Sub ShowIndex() On Error Resume Next MsgBox Selection.Interior.ColorIndex End Sub It works fine if the colors are manually set. But - if they are set by conditional formatting then wierd things happen. I set up a trial sheet in which the first 10 cells of column A were colored green for positive values and red for negative values. When I actually change the values from positive to negative or vice versa I get (when I run the ShowIndex sub on various cells) that all colorindices are -4142. Maybe that is some alias for xlAutomatic or something like that and the conditional formatting in effect doesn't change the color per se but changes the meaning of automatic for that cell. But then - this raises the question: how can you determine the color of a cell if its color has been set by conditional formatting? I guess you would have to determine programmatically which condition applies and go inside the corresponding formatcondition object - which seems like a lot of effort to read what should be an easy property to read off. -John Coleman Bob Phillips wrote: SUM them based upon the same condition that is applied to CF. For instance, if the CF is greater than 10, then use =SUMIF(A1:A10,"10") -- --- HTH Bob (change the xxxx to gmail if mailing direct) wrote in message ups.com... Hi, I am not sure Excel 2003 can do this or not. Here is what I would like to do. I have a column (say column A) with some numbers. Some of the cells are colored green and some are colored red. I would like to add all the cells colored in red at cell B1 and add all the cells colored in green at cell C1. The color may be changed dynamically by means of conditional formatting. Any idea? Thanks. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Computing
A few seconds after I posted I realized that it is obvious that
conditional formatting shouldn't change the colorindex - this allows the original colorindex to stick around as a default color when none of the conditions apply. It would be nice if cells had a DisplayedColorIndex property in addition to a ColorIndex one. I'll have to write my own function. No need to answer my previous post. John Coleman wrote: Bob, Your advice is probably the way to go, but do you have any idea why the following doesn't work as intended? Function ColorSum(R As Range, i As Long) As Variant Dim sum As Variant Dim cl As Range For Each cl In R.Cells If cl.Interior.ColorIndex = i Then sum = sum + cl.Value End If Next cl ColorSum = sum End Function Sub RedGreenSums() Dim R As Range Set R = Range("A1", Cells(Rows.Count, 1).End(xlUp)) Range("B1").Value = ColorSum(R, 3) Range("C1").Value = ColorSum(R, 10) End Sub Private Sub Worksheet_Calculate() RedGreenSums End Sub 'For debugging purposes: Sub ShowIndex() On Error Resume Next MsgBox Selection.Interior.ColorIndex End Sub It works fine if the colors are manually set. But - if they are set by conditional formatting then wierd things happen. I set up a trial sheet in which the first 10 cells of column A were colored green for positive values and red for negative values. When I actually change the values from positive to negative or vice versa I get (when I run the ShowIndex sub on various cells) that all colorindices are -4142. Maybe that is some alias for xlAutomatic or something like that and the conditional formatting in effect doesn't change the color per se but changes the meaning of automatic for that cell. But then - this raises the question: how can you determine the color of a cell if its color has been set by conditional formatting? I guess you would have to determine programmatically which condition applies and go inside the corresponding formatcondition object - which seems like a lot of effort to read what should be an easy property to read off. -John Coleman Bob Phillips wrote: SUM them based upon the same condition that is applied to CF. For instance, if the CF is greater than 10, then use =SUMIF(A1:A10,"10") -- --- HTH Bob (change the xxxx to gmail if mailing direct) wrote in message ups.com... Hi, I am not sure Excel 2003 can do this or not. Here is what I would like to do. I have a column (say column A) with some numbers. Some of the cells are colored green and some are colored red. I would like to add all the cells colored in red at cell B1 and add all the cells colored in green at cell C1. The color may be changed dynamically by means of conditional formatting. Any idea? Thanks. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Computing
John,
They do, that is what the CF colour is. It is difficult, but not impossible to get the CF colour. See http://xldynamic.com/source/xld.CFConditions.html -- --- HTH Bob (change the xxxx to gmail if mailing direct) "John Coleman" wrote in message oups.com... A few seconds after I posted I realized that it is obvious that conditional formatting shouldn't change the colorindex - this allows the original colorindex to stick around as a default color when none of the conditions apply. It would be nice if cells had a DisplayedColorIndex property in addition to a ColorIndex one. I'll have to write my own function. No need to answer my previous post. John Coleman wrote: Bob, Your advice is probably the way to go, but do you have any idea why the following doesn't work as intended? Function ColorSum(R As Range, i As Long) As Variant Dim sum As Variant Dim cl As Range For Each cl In R.Cells If cl.Interior.ColorIndex = i Then sum = sum + cl.Value End If Next cl ColorSum = sum End Function Sub RedGreenSums() Dim R As Range Set R = Range("A1", Cells(Rows.Count, 1).End(xlUp)) Range("B1").Value = ColorSum(R, 3) Range("C1").Value = ColorSum(R, 10) End Sub Private Sub Worksheet_Calculate() RedGreenSums End Sub 'For debugging purposes: Sub ShowIndex() On Error Resume Next MsgBox Selection.Interior.ColorIndex End Sub It works fine if the colors are manually set. But - if they are set by conditional formatting then wierd things happen. I set up a trial sheet in which the first 10 cells of column A were colored green for positive values and red for negative values. When I actually change the values from positive to negative or vice versa I get (when I run the ShowIndex sub on various cells) that all colorindices are -4142. Maybe that is some alias for xlAutomatic or something like that and the conditional formatting in effect doesn't change the color per se but changes the meaning of automatic for that cell. But then - this raises the question: how can you determine the color of a cell if its color has been set by conditional formatting? I guess you would have to determine programmatically which condition applies and go inside the corresponding formatcondition object - which seems like a lot of effort to read what should be an easy property to read off. -John Coleman Bob Phillips wrote: SUM them based upon the same condition that is applied to CF. For instance, if the CF is greater than 10, then use =SUMIF(A1:A10,"10") -- --- HTH Bob (change the xxxx to gmail if mailing direct) wrote in message ups.com... Hi, I am not sure Excel 2003 can do this or not. Here is what I would like to do. I have a column (say column A) with some numbers. Some of the cells are colored green and some are colored red. I would like to add all the cells colored in red at cell B1 and add all the cells colored in green at cell C1. The color may be changed dynamically by means of conditional formatting. Any idea? Thanks. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Computing
Bob,
Impressive. I find it amazing how involved the code is. Conditional formats ultimately involve a simple boolean value that the application has stored *somewhere*, and given how important conditional formatting is in many spreadsheets it is disappointing that this value is not exposed in the object model. You are to be commended in being able to discover a work-around. Thanks for the link! -John Coleman Bob Phillips wrote: John, They do, that is what the CF colour is. It is difficult, but not impossible to get the CF colour. See http://xldynamic.com/source/xld.CFConditions.html -- --- HTH Bob (change the xxxx to gmail if mailing direct) "John Coleman" wrote in message oups.com... A few seconds after I posted I realized that it is obvious that conditional formatting shouldn't change the colorindex - this allows the original colorindex to stick around as a default color when none of the conditions apply. It would be nice if cells had a DisplayedColorIndex property in addition to a ColorIndex one. I'll have to write my own function. No need to answer my previous post. John Coleman wrote: Bob, Your advice is probably the way to go, but do you have any idea why the following doesn't work as intended? Function ColorSum(R As Range, i As Long) As Variant Dim sum As Variant Dim cl As Range For Each cl In R.Cells If cl.Interior.ColorIndex = i Then sum = sum + cl.Value End If Next cl ColorSum = sum End Function Sub RedGreenSums() Dim R As Range Set R = Range("A1", Cells(Rows.Count, 1).End(xlUp)) Range("B1").Value = ColorSum(R, 3) Range("C1").Value = ColorSum(R, 10) End Sub Private Sub Worksheet_Calculate() RedGreenSums End Sub 'For debugging purposes: Sub ShowIndex() On Error Resume Next MsgBox Selection.Interior.ColorIndex End Sub It works fine if the colors are manually set. But - if they are set by conditional formatting then wierd things happen. I set up a trial sheet in which the first 10 cells of column A were colored green for positive values and red for negative values. When I actually change the values from positive to negative or vice versa I get (when I run the ShowIndex sub on various cells) that all colorindices are -4142. Maybe that is some alias for xlAutomatic or something like that and the conditional formatting in effect doesn't change the color per se but changes the meaning of automatic for that cell. But then - this raises the question: how can you determine the color of a cell if its color has been set by conditional formatting? I guess you would have to determine programmatically which condition applies and go inside the corresponding formatcondition object - which seems like a lot of effort to read what should be an easy property to read off. -John Coleman Bob Phillips wrote: SUM them based upon the same condition that is applied to CF. For instance, if the CF is greater than 10, then use =SUMIF(A1:A10,"10") -- --- HTH Bob (change the xxxx to gmail if mailing direct) wrote in message ups.com... Hi, I am not sure Excel 2003 can do this or not. Here is what I would like to do. I have a column (say column A) with some numbers. Some of the cells are colored green and some are colored red. I would like to add all the cells colored in red at cell B1 and add all the cells colored in green at cell C1. The color may be changed dynamically by means of conditional formatting. Any idea? Thanks. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Computing
John,
The big problem lies in what is actually a feature of CF. If you select a range of cells and apply CF, it will adjust any formula presented relative to the position of the cell in the selection. This is a very convenient way of selecting a range and using say =AND(A1B1,C1TODAY()) next cell will adjust the row or column as appropriate. As I said, it is very useful as it allows you to setup multiple cells at once, but the CF is relative. And when you are not in that cell, you have to make an adjustment for where the CF is being evaluated from. Therein lies the difficulty, and thus the coding complexity. -- --- HTH Bob (change the xxxx to gmail if mailing direct) "John Coleman" wrote in message ups.com... Bob, Impressive. I find it amazing how involved the code is. Conditional formats ultimately involve a simple boolean value that the application has stored *somewhere*, and given how important conditional formatting is in many spreadsheets it is disappointing that this value is not exposed in the object model. You are to be commended in being able to discover a work-around. Thanks for the link! -John Coleman Bob Phillips wrote: John, They do, that is what the CF colour is. It is difficult, but not impossible to get the CF colour. See http://xldynamic.com/source/xld.CFConditions.html -- --- HTH Bob (change the xxxx to gmail if mailing direct) "John Coleman" wrote in message oups.com... A few seconds after I posted I realized that it is obvious that conditional formatting shouldn't change the colorindex - this allows the original colorindex to stick around as a default color when none of the conditions apply. It would be nice if cells had a DisplayedColorIndex property in addition to a ColorIndex one. I'll have to write my own function. No need to answer my previous post. John Coleman wrote: Bob, Your advice is probably the way to go, but do you have any idea why the following doesn't work as intended? Function ColorSum(R As Range, i As Long) As Variant Dim sum As Variant Dim cl As Range For Each cl In R.Cells If cl.Interior.ColorIndex = i Then sum = sum + cl.Value End If Next cl ColorSum = sum End Function Sub RedGreenSums() Dim R As Range Set R = Range("A1", Cells(Rows.Count, 1).End(xlUp)) Range("B1").Value = ColorSum(R, 3) Range("C1").Value = ColorSum(R, 10) End Sub Private Sub Worksheet_Calculate() RedGreenSums End Sub 'For debugging purposes: Sub ShowIndex() On Error Resume Next MsgBox Selection.Interior.ColorIndex End Sub It works fine if the colors are manually set. But - if they are set by conditional formatting then wierd things happen. I set up a trial sheet in which the first 10 cells of column A were colored green for positive values and red for negative values. When I actually change the values from positive to negative or vice versa I get (when I run the ShowIndex sub on various cells) that all colorindices are -4142. Maybe that is some alias for xlAutomatic or something like that and the conditional formatting in effect doesn't change the color per se but changes the meaning of automatic for that cell. But then - this raises the question: how can you determine the color of a cell if its color has been set by conditional formatting? I guess you would have to determine programmatically which condition applies and go inside the corresponding formatcondition object - which seems like a lot of effort to read what should be an easy property to read off. -John Coleman Bob Phillips wrote: SUM them based upon the same condition that is applied to CF. For instance, if the CF is greater than 10, then use =SUMIF(A1:A10,"10") -- --- HTH Bob (change the xxxx to gmail if mailing direct) wrote in message ups.com... Hi, I am not sure Excel 2003 can do this or not. Here is what I would like to do. I have a column (say column A) with some numbers. Some of the cells are colored green and some are colored red. I would like to add all the cells colored in red at cell B1 and add all the cells colored in green at cell C1. The color may be changed dynamically by means of conditional formatting. Any idea? Thanks. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Computing
Very useful stuff Bob, Thanks.
NickH |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Computing
Hi Guys,
Thank you so much for all your help. Very impressive indeed. Aung NickH wrote: Very useful stuff Bob, Thanks. NickH |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Computing
Hi John,
I tried using Conditional Sum suggested by you and its purpose is not the way I wanted. In fact, here is what I am trying to achieve. Let's say I have a list of expenses (10 items) in column A. 3 items have been paid and therefore I will color them in green. 4 items are due to pay in 2 days time and therefore I will color them in red. 3 other items can be paid later. What I would like to do is based on the color; I would like to compute total amount I have paid, total amount I need to pay in 2 days and remaining total amount. Any good idea? Thank you! Aung John Coleman wrote: Hi, You can do this in VBA of course (hint: use the worksheet_calculate event). But - before you go that rout, have you considered using the conditional sum wizard? It is designed to create conditional sum formulas using the same sorts of criteria as are used in conditional formatting. From your problem description, this seems like a good possibility. It is an add in. Go to tools - add ins and install it if it isn't already. This may be better than a VBA approach since, for example, the code might break if you decide you want to use different colors in the conditional formatting. It would probably also be quicker if you are talking about a large number of cells. HTH -John Coleman wrote: Hi, I am not sure Excel 2003 can do this or not. Here is what I would like to do. I have a column (say column A) with some numbers. Some of the cells are colored green and some are colored red. I would like to add all the cells colored in red at cell B1 and add all the cells colored in green at cell C1. The color may be changed dynamically by means of conditional formatting. Any idea? Thanks. |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Computing
Hi
I think we may try to use the VBA to sum the values. 1. the text of the value is Red. Sub SumFontRed() Dim rg As Range Dim i As Integer For i = 2 To 7 Dim sum As Integer Set rg = Cells(i, 1) If rg.Font.ColorIndex = 3 Then sum = sum + rg.Value2 End If Next MsgBox sum End Sub 2. the backgroud of the cell is in Red. Sub SumBackgroudRed() Dim rg As Range Dim i As Integer For i = 2 To 7 Dim sum As Integer Set rg = Cells(i, 1) If rg.Interior.ColorIndex = 3 Then sum = sum + rg.Value2 End If Next MsgBox sum End Sub NOTE: here the ColorIndex = 3 means it is red. To get the value conveniently, we may try to use the Record Macro function to record a macro to see when we set the text to red, what is the macro code recorded. If you have any concern on this issue, pleaes feel free to let me know. Thanks! Best regards, Perter Huang Microsoft Online Partner Support Get Secure! - www.microsoft.com/security This posting is provided "AS IS" with no warranties, and confers no rights. |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Computing
Peter,
The OP mentioned that the colours would change dynamically with conditional formatting. That code you show will not get this, it gets the cell/text colour. See other posts in this thread to see how that needs to be addressed. -- --- HTH Bob (change the xxxx to gmail if mailing direct) ""Peter Huang" [MSFT]" wrote in message ... Hi I think we may try to use the VBA to sum the values. 1. the text of the value is Red. Sub SumFontRed() Dim rg As Range Dim i As Integer For i = 2 To 7 Dim sum As Integer Set rg = Cells(i, 1) If rg.Font.ColorIndex = 3 Then sum = sum + rg.Value2 End If Next MsgBox sum End Sub 2. the backgroud of the cell is in Red. Sub SumBackgroudRed() Dim rg As Range Dim i As Integer For i = 2 To 7 Dim sum As Integer Set rg = Cells(i, 1) If rg.Interior.ColorIndex = 3 Then sum = sum + rg.Value2 End If Next MsgBox sum End Sub NOTE: here the ColorIndex = 3 means it is red. To get the value conveniently, we may try to use the Record Macro function to record a macro to see when we set the text to red, what is the macro code recorded. If you have any concern on this issue, pleaes feel free to let me know. Thanks! Best regards, Perter Huang Microsoft Online Partner Support Get Secure! - www.microsoft.com/security This posting is provided "AS IS" with no warranties, and confers no rights. |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Computing
John,
Reverting back on one of your statements about a simple Boolean stored somewhere, whilst we might think that the object model should contain such a property, I don't believe that it does. CF is effectively volatile, that is it is evaluated every recalculation causes CF to be re-evaluated. It is this that makes me think that Excel does the same sort of determination that my code does. I guess it is a trade-off, re-calculate all of the CF conditions every time the range/sheet/book is recalculated, or every time a dependent or precedent cell involved in the CF is changed. Whilst I think it is the former, it would certainly be preferable IMO if it were the latter, our functions would then recalculate when the CF caused a state change, which we cannot do now. Regards Bob "John Coleman" wrote in message ups.com... Bob, Impressive. I find it amazing how involved the code is. Conditional formats ultimately involve a simple boolean value that the application has stored *somewhere*, and given how important conditional formatting is in many spreadsheets it is disappointing that this value is not exposed in the object model. You are to be commended in being able to discover a work-around. Thanks for the link! -John Coleman Bob Phillips wrote: John, They do, that is what the CF colour is. It is difficult, but not impossible to get the CF colour. See http://xldynamic.com/source/xld.CFConditions.html -- --- HTH Bob (change the xxxx to gmail if mailing direct) "John Coleman" wrote in message oups.com... A few seconds after I posted I realized that it is obvious that conditional formatting shouldn't change the colorindex - this allows the original colorindex to stick around as a default color when none of the conditions apply. It would be nice if cells had a DisplayedColorIndex property in addition to a ColorIndex one. I'll have to write my own function. No need to answer my previous post. John Coleman wrote: Bob, Your advice is probably the way to go, but do you have any idea why the following doesn't work as intended? Function ColorSum(R As Range, i As Long) As Variant Dim sum As Variant Dim cl As Range For Each cl In R.Cells If cl.Interior.ColorIndex = i Then sum = sum + cl.Value End If Next cl ColorSum = sum End Function Sub RedGreenSums() Dim R As Range Set R = Range("A1", Cells(Rows.Count, 1).End(xlUp)) Range("B1").Value = ColorSum(R, 3) Range("C1").Value = ColorSum(R, 10) End Sub Private Sub Worksheet_Calculate() RedGreenSums End Sub 'For debugging purposes: Sub ShowIndex() On Error Resume Next MsgBox Selection.Interior.ColorIndex End Sub It works fine if the colors are manually set. But - if they are set by conditional formatting then wierd things happen. I set up a trial sheet in which the first 10 cells of column A were colored green for positive values and red for negative values. When I actually change the values from positive to negative or vice versa I get (when I run the ShowIndex sub on various cells) that all colorindices are -4142. Maybe that is some alias for xlAutomatic or something like that and the conditional formatting in effect doesn't change the color per se but changes the meaning of automatic for that cell. But then - this raises the question: how can you determine the color of a cell if its color has been set by conditional formatting? I guess you would have to determine programmatically which condition applies and go inside the corresponding formatcondition object - which seems like a lot of effort to read what should be an easy property to read off. -John Coleman Bob Phillips wrote: SUM them based upon the same condition that is applied to CF. For instance, if the CF is greater than 10, then use =SUMIF(A1:A10,"10") -- --- HTH Bob (change the xxxx to gmail if mailing direct) wrote in message ups.com... Hi, I am not sure Excel 2003 can do this or not. Here is what I would like to do. I have a column (say column A) with some numbers. Some of the cells are colored green and some are colored red. I would like to add all the cells colored in red at cell B1 and add all the cells colored in green at cell C1. The color may be changed dynamically by means of conditional formatting. Any idea? Thanks. |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Computing
Nick,
This post taught me something I never realized. I'm so used to thinking of user-defined-functions as needing to be pure functions - limited to returning a value which can be displayed in a cell and with no side-effects involving formatting, etc. - that I had implicitly assumed that UDFs couldn't even involve formatting. Hence my somewhat round-about suggestion of linking my version of the colorsum function to the calculate event. But when you wrote "To use enter something like =ColorSum(A1:A20,10) into a cell." I realized that the only restriction was on the "output" of the UDF but that the "input" can involve virtually anything. I'm not quite sure where I would use that fact, but I'm sure that sooner or later it would come in handy. Thanks. -John NickH wrote: I think John's probably right, especially if you are already using Excel's built-in Conditional Formatting. However, should you decide to go the VBA route the following functions may be of some help. NB. These functions will NOT detect a cell interior colour that has been set using conditional formatting. To do that you would need to write code to interrogate the condtional format settings, in which case you might as well write your own conditional formatting routine (yeah, listen to John). Still reading? Okay go here for some Conditional Format macro suggestions... http://www.mvps.org/dmcritchie/excel/condfmt.htm Here's a function to do the summing... Public Function ColorSum(mRng As Range, mColor As Integer) As Single Dim mTot As Single Dim c As Range For Each c In mRng If IsNumeric(c.Value) Then If c.Interior.ColorIndex = mColor Then mTot = mTot + c.Value End If End If Next c ColorSum = mTot End Function To use enter something like =ColorSum(A1:A20,10) into a cell. And here's a function to return the interior colour index of a cell... Public Function GetColorIndex(mCell As Range) As Integer ''' Quick check to find the interior color of a cell. _ If multiple cells selected only top left examined GetColorIndex = mCell.Range("A1").Interior.ColorIndex End Function To use enter something like =GetColorIndex(A3) into a cell. Hope you listened to John - I'm just killing time. ?;^) NickH |
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Computing
Hi Bob,
I think this can be adpated by add a paramater in the macro. e.g. Sub SumFontRed(ByVal color As Integer) ..... rg.Font.ColorIndex = color ..... End Sub Best regards, Perter Huang Microsoft Online Partner Support Get Secure! - www.microsoft.com/security This posting is provided "AS IS" with no warranties, and confers no rights. |
#19
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Computing
John,
Thank you so much for all your post. I can ignore conditional formatting for this moment and work with VBA code provided earlier. Thanks! Aung John Coleman wrote: Aung, Are all of the colors under the control of conditional formatting? If so - the way to go is to concentrate on the conditions rather than the colors. If neither SUMIF not the conditional sum wizard is sufficienlty flexible (though this seems unlikely from your brief description), then a VBA function that loops through the cells testing the relevant condition is the way to go. If none of the colors are under the control of conditional formatting, then there again is a pretty easy solution based on looping through the cells and checking colorindices. 3 posters, including myself in my second post, have given variations of the same code for this. It is when there is a mixture of the two cases that things would start to get dicey. It might require the somewhat involved strategy of downloading Bob's code to analyze conditional formatting and integrating it with the colorindex approach. Or - it *might* have a local-tech approach. If you start by manually coloring cells and then putting conditions on top of (some?) of those colors then perhaps the manual colors can be thought of as providing a baseline sum which can be computed via the colorindex approach and then the conditional part can be thought of as providing a correction term which is computed via SUMIF and then added or subtracted to the baseline. This seems overly complex to me - if you have a case of a mixture of manual and conditional colors, see if you can make *all* of the color explicitly conditional by adding a default condition (which shouldn't be much of a problem unless you hit the three condition limit ) then try to use a SUMIF approach. So - just what role does conditional formatting play in your situation? -John Coleman wrote: Hi John, I tried using Conditional Sum suggested by you and its purpose is not the way I wanted. In fact, here is what I am trying to achieve. Let's say I have a list of expenses (10 items) in column A. 3 items have been paid and therefore I will color them in green. 4 items are due to pay in 2 days time and therefore I will color them in red. 3 other items can be paid later. What I would like to do is based on the color; I would like to compute total amount I have paid, total amount I need to pay in 2 days and remaining total amount. Any good idea? Thank you! Aung John Coleman wrote: Hi, You can do this in VBA of course (hint: use the worksheet_calculate event). But - before you go that rout, have you considered using the conditional sum wizard? It is designed to create conditional sum formulas using the same sorts of criteria as are used in conditional formatting. From your problem description, this seems like a good possibility. It is an add in. Go to tools - add ins and install it if it isn't already. This may be better than a VBA approach since, for example, the code might break if you decide you want to use different colors in the conditional formatting. It would probably also be quicker if you are talking about a large number of cells. HTH -John Coleman wrote: Hi, I am not sure Excel 2003 can do this or not. Here is what I would like to do. I have a column (say column A) with some numbers. Some of the cells are colored green and some are colored red. I would like to add all the cells colored in red at cell B1 and add all the cells colored in green at cell C1. The color may be changed dynamically by means of conditional formatting. Any idea? Thanks. |
#20
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Computing
Hi Peter,
I don't see how that suddenly addresses CF colour, it may be more flexible but it is still cell colour. -- --- HTH Bob (change the xxxx to gmail if mailing direct) ""Peter Huang" [MSFT]" wrote in message ... Hi Bob, I think this can be adpated by add a paramater in the macro. e.g. Sub SumFontRed(ByVal color As Integer) .... rg.Font.ColorIndex = color .... End Sub Best regards, Perter Huang Microsoft Online Partner Support Get Secure! - www.microsoft.com/security This posting is provided "AS IS" with no warranties, and confers no rights. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Computing mortgage APR | Excel Worksheet Functions | |||
Computing Time from One Day to Another | Excel Discussion (Misc queries) | |||
Conditional Computing | Excel Worksheet Functions | |||
COUNTIF not computing | Excel Discussion (Misc queries) | |||
Negative Value not computing | Excel Programming |