If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
A UDF for Counting Coloured Cells that are conditionally formatted
Hi All,
I have been doing some extensive research and testing to determine whether there is any way that a UDF can be created that will return the count for cells on an input sheet that show a particular colour (as determined by CF). So far it would appear that this is not possible. I have accessed some of the most eminent MVPs and VBA gurus, but the conclusion would appear to be (and borne out by my testing) "No - can't be done" So this is my last attempt to find a solution - if it can't be done I can laboriously go throgh my 50 odd input sheets and mirror the CF conditions in a helper area and return a value of 1 each time the (complex) CF criteria is met - count therefore now possible; however, I would much prefer a UDF if possible. On a more general note this requirement seems to crop up regularly with developers, so I wonder if this is not something that MS should be addressing directly? Please find below the original question - and code blocks - that I put up. Any help would be much appreciated - even it just confirms that this is not possible. Many thanks. Neil "Does anyone have the robust code for a UDF that can be used to count the number of cells that conform to a certain (conditional) colour formatting (in Excel 2007) I found the following: Function CountColor(Color As Range, Range As Range) As Long Dim C As Range For Each C In Range If C.Interior.ColorIndex = Color.Interior.ColorIndex Then CountColor = CountColor + 1 End If Next End Function but this does not work consistently, though, of the 3 I did download, (1 from Ozgrid and 1 from Mr.Excel.com) this is the only one which did appear to function, albeit in limited conditions. I am using conditional formatting to draw the Users attention to invalid entries and ideally want to create a "one stop shop" report which the User can access easily to see if there are any invalid entries on any of the 50 plus input sheets i.e. without having to trawl through them individually..... The Ozgrid code is: Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean) Dim rCell As Range Dim lCol As Long Dim vResult '''''''''''''''''''''''''''''''''''''' 'Written by Ozgrid Business Applications 'www.ozgrid.com 'Sums or counts cells based on a specified fill color. 'You can now use the custom function (ColorFunction) like; '=ColorFunction($C$1,$A$1:$A$12,TRUE) to SUM the values in range of cells $A$1:$A$12 'that have the same fill color as cell $C$1. The reason it will SUM in this example is because 'we have used TRUE as the last argument for the custom function. 'To COUNT these cells that have the same fill color as cell $C$1 you could use: '=ColorFunction($C$1,$A$1:$A$12,FALSE) or =ColorFunction($C$1,$A$1:$A$12) by omitting the last argument 'our function will automatically default to using FALSE. 'Be aware that the changing of a cells fill color will not cause the Custom Function to recalculate, 'even if you press F9 (Recalculates the whole Workbook). You will need to either, select the cell 'and re-enter the formula, or go to EditReplace and replace = with =, or use Ctrl+Alt+F9 ''''''''''''''''''''''''''''''''''''''' lCol = rColor.Interior.ColorIndex If SUM = True Then For Each rCell In rRange If rCell.Interior.ColorIndex = lCol Then vResult = WorksheetFunction.SUM(rCell, vResult) End If Next rCell Else For Each rCell In rRange If rCell.Interior.ColorIndex = lCol Then vResult = 1 + vResult End If Next rCell End If ColorFunction = vResult End Function and, whilst this seemed to work partially, it doesn't if the cell formats change, which they will of course, and neither does it respond to F9 (re-calculate), as the author correctly points out. Any guidance you can provide will be much appreciated. Thank you." |
#2
|
|||
|
|||
A UDF for Counting Coloured Cells that are conditionally formatted
Check Chip Pearson's site:
http://www.cpearson.com/Excel/CFColors.htm But it's not for the faint of heart. In fact, I'd use those mirror cells and then use them for the conditional formatting rules for the real cells (no sense updating both sets of formulas when something changes). ps. I think I've seen where xl2010 is going to have a .displayformat (or something like that) property that will make this easier. NeilG wrote: Hi All, I have been doing some extensive research and testing to determine whether there is any way that a UDF can be created that will return the count for cells on an input sheet that show a particular colour (as determined by CF). So far it would appear that this is not possible. I have accessed some of the most eminent MVPs and VBA gurus, but the conclusion would appear to be (and borne out by my testing) "No - can't be done" So this is my last attempt to find a solution - if it can't be done I can laboriously go throgh my 50 odd input sheets and mirror the CF conditions in a helper area and return a value of 1 each time the (complex) CF criteria is met - count therefore now possible; however, I would much prefer a UDF if possible. On a more general note this requirement seems to crop up regularly with developers, so I wonder if this is not something that MS should be addressing directly? Please find below the original question - and code blocks - that I put up. Any help would be much appreciated - even it just confirms that this is not possible. Many thanks. Neil "Does anyone have the robust code for a UDF that can be used to count the number of cells that conform to a certain (conditional) colour formatting (in Excel 2007) I found the following: Function CountColor(Color As Range, Range As Range) As Long Dim C As Range For Each C In Range If C.Interior.ColorIndex = Color.Interior.ColorIndex Then CountColor = CountColor + 1 End If Next End Function but this does not work consistently, though, of the 3 I did download, (1 from Ozgrid and 1 from Mr.Excel.com) this is the only one which did appear to function, albeit in limited conditions. I am using conditional formatting to draw the Users attention to invalid entries and ideally want to create a "one stop shop" report which the User can access easily to see if there are any invalid entries on any of the 50 plus input sheets i.e. without having to trawl through them individually..... The Ozgrid code is: Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean) Dim rCell As Range Dim lCol As Long Dim vResult '''''''''''''''''''''''''''''''''''''' 'Written by Ozgrid Business Applications 'www.ozgrid.com 'Sums or counts cells based on a specified fill color. 'You can now use the custom function (ColorFunction) like; '=ColorFunction($C$1,$A$1:$A$12,TRUE) to SUM the values in range of cells $A$1:$A$12 'that have the same fill color as cell $C$1. The reason it will SUM in this example is because 'we have used TRUE as the last argument for the custom function. 'To COUNT these cells that have the same fill color as cell $C$1 you could use: '=ColorFunction($C$1,$A$1:$A$12,FALSE) or =ColorFunction($C$1,$A$1:$A$12) by omitting the last argument 'our function will automatically default to using FALSE. 'Be aware that the changing of a cells fill color will not cause the Custom Function to recalculate, 'even if you press F9 (Recalculates the whole Workbook). You will need to either, select the cell 'and re-enter the formula, or go to EditReplace and replace = with =, or use Ctrl+Alt+F9 ''''''''''''''''''''''''''''''''''''''' lCol = rColor.Interior.ColorIndex If SUM = True Then For Each rCell In rRange If rCell.Interior.ColorIndex = lCol Then vResult = WorksheetFunction.SUM(rCell, vResult) End If Next rCell Else For Each rCell In rRange If rCell.Interior.ColorIndex = lCol Then vResult = 1 + vResult End If Next rCell End If ColorFunction = vResult End Function and, whilst this seemed to work partially, it doesn't if the cell formats change, which they will of course, and neither does it respond to F9 (re-calculate), as the author correctly points out. Any guidance you can provide will be much appreciated. Thank you." -- Dave Peterson |
#3
|
|||
|
|||
A UDF for Counting Coloured Cells that are conditionally formatted
NeilG wrote:
Hi All, I have been doing some extensive research and testing to determine whether there is any way that a UDF can be created that will return the count for cells on an input sheet that show a particular colour (as determined by CF). So far it would appear that this is not possible. I have accessed some of the most eminent MVPs and VBA gurus, but the conclusion would appear to be (and borne out by my testing) "No - can't be done" So this is my last attempt to find a solution - if it can't be done I can laboriously go throgh my 50 odd input sheets and mirror the CF conditions in a helper area and return a value of 1 each time the (complex) CF criteria is met - count therefore now possible; however, I would much prefer a UDF if possible. On a more general note this requirement seems to crop up regularly with developers, so I wonder if this is not something that MS should be addressing directly? Please find below the original question - and code blocks - that I put up. Any help would be much appreciated - even it just confirms that this is not possible. Many thanks. Neil "Does anyone have the robust code for a UDF that can be used to count the number of cells that conform to a certain (conditional) colour formatting (in Excel 2007) I found the following: Function CountColor(Color As Range, Range As Range) As Long Dim C As Range For Each C In Range If C.Interior.ColorIndex = Color.Interior.ColorIndex Then CountColor = CountColor + 1 End If Next End Function but this does not work consistently, though, of the 3 I did download, (1 from Ozgrid and 1 from Mr.Excel.com) this is the only one which did appear to function, albeit in limited conditions. I am using conditional formatting to draw the Users attention to invalid entries and ideally want to create a "one stop shop" report which the User can access easily to see if there are any invalid entries on any of the 50 plus input sheets i.e. without having to trawl through them individually..... The Ozgrid code is: Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean) Dim rCell As Range Dim lCol As Long Dim vResult '''''''''''''''''''''''''''''''''''''' 'Written by Ozgrid Business Applications 'www.ozgrid.com 'Sums or counts cells based on a specified fill color. 'You can now use the custom function (ColorFunction) like; '=ColorFunction($C$1,$A$1:$A$12,TRUE) to SUM the values in range of cells $A$1:$A$12 'that have the same fill color as cell $C$1. The reason it will SUM in this example is because 'we have used TRUE as the last argument for the custom function. 'To COUNT these cells that have the same fill color as cell $C$1 you could use: '=ColorFunction($C$1,$A$1:$A$12,FALSE) or =ColorFunction($C$1,$A$1:$A$12) by omitting the last argument 'our function will automatically default to using FALSE. 'Be aware that the changing of a cells fill color will not cause the Custom Function to recalculate, 'even if you press F9 (Recalculates the whole Workbook). You will need to either, select the cell 'and re-enter the formula, or go to EditReplace and replace = with =, or use Ctrl+Alt+F9 ''''''''''''''''''''''''''''''''''''''' lCol = rColor.Interior.ColorIndex If SUM = True Then For Each rCell In rRange If rCell.Interior.ColorIndex = lCol Then vResult = WorksheetFunction.SUM(rCell, vResult) End If Next rCell Else For Each rCell In rRange If rCell.Interior.ColorIndex = lCol Then vResult = 1 + vResult End If Next rCell End If ColorFunction = vResult End Function and, whilst this seemed to work partially, it doesn't if the cell formats change, which they will of course, and neither does it respond to F9 (re-calculate), as the author correctly points out. Any guidance you can provide will be much appreciated. Thank you." I use a program called ASAP Utilities. It's a freebie. I find this little add on quite helpful. http://www.asap-utilities.com/index.php It contains quite a number of preprogrammed UDF's While I've never used this function, in their online doc on page 122 they list the function below: ==ASAPCountShades(range) Not sure that's what you want but it's worth a look. http://www.asap-utilities.com/docume...user-guide.php gls858 |
#4
|
|||
|
|||
A UDF for Counting Coloured Cells that are conditionally formatted
I agree, it would be nice if there was a simple way to check "current
displayed color" in a cell. As it is, it appears the closest workaround is to determine which condition is currently active. Assuming the colors that you are trying to count come from similar based CF's (the color red always comes from condition 1?) you might check out this site: http://www.cpearson.com/excel/CFColors.htm I admit I didn't have time to test everything, but the intro paragraph sounds like it provides the correct UDF's to accomplish your end goal. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "NeilG" wrote: Hi All, I have been doing some extensive research and testing to determine whether there is any way that a UDF can be created that will return the count for cells on an input sheet that show a particular colour (as determined by CF). So far it would appear that this is not possible. I have accessed some of the most eminent MVPs and VBA gurus, but the conclusion would appear to be (and borne out by my testing) "No - can't be done" So this is my last attempt to find a solution - if it can't be done I can laboriously go throgh my 50 odd input sheets and mirror the CF conditions in a helper area and return a value of 1 each time the (complex) CF criteria is met - count therefore now possible; however, I would much prefer a UDF if possible. On a more general note this requirement seems to crop up regularly with developers, so I wonder if this is not something that MS should be addressing directly? Please find below the original question - and code blocks - that I put up. Any help would be much appreciated - even it just confirms that this is not possible. Many thanks. Neil "Does anyone have the robust code for a UDF that can be used to count the number of cells that conform to a certain (conditional) colour formatting (in Excel 2007) I found the following: Function CountColor(Color As Range, Range As Range) As Long Dim C As Range For Each C In Range If C.Interior.ColorIndex = Color.Interior.ColorIndex Then CountColor = CountColor + 1 End If Next End Function but this does not work consistently, though, of the 3 I did download, (1 from Ozgrid and 1 from Mr.Excel.com) this is the only one which did appear to function, albeit in limited conditions. I am using conditional formatting to draw the Users attention to invalid entries and ideally want to create a "one stop shop" report which the User can access easily to see if there are any invalid entries on any of the 50 plus input sheets i.e. without having to trawl through them individually..... The Ozgrid code is: Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean) Dim rCell As Range Dim lCol As Long Dim vResult '''''''''''''''''''''''''''''''''''''' 'Written by Ozgrid Business Applications 'www.ozgrid.com 'Sums or counts cells based on a specified fill color. 'You can now use the custom function (ColorFunction) like; '=ColorFunction($C$1,$A$1:$A$12,TRUE) to SUM the values in range of cells $A$1:$A$12 'that have the same fill color as cell $C$1. The reason it will SUM in this example is because 'we have used TRUE as the last argument for the custom function. 'To COUNT these cells that have the same fill color as cell $C$1 you could use: '=ColorFunction($C$1,$A$1:$A$12,FALSE) or =ColorFunction($C$1,$A$1:$A$12) by omitting the last argument 'our function will automatically default to using FALSE. 'Be aware that the changing of a cells fill color will not cause the Custom Function to recalculate, 'even if you press F9 (Recalculates the whole Workbook). You will need to either, select the cell 'and re-enter the formula, or go to EditReplace and replace = with =, or use Ctrl+Alt+F9 ''''''''''''''''''''''''''''''''''''''' lCol = rColor.Interior.ColorIndex If SUM = True Then For Each rCell In rRange If rCell.Interior.ColorIndex = lCol Then vResult = WorksheetFunction.SUM(rCell, vResult) End If Next rCell Else For Each rCell In rRange If rCell.Interior.ColorIndex = lCol Then vResult = 1 + vResult End If Next rCell End If ColorFunction = vResult End Function and, whilst this seemed to work partially, it doesn't if the cell formats change, which they will of course, and neither does it respond to F9 (re-calculate), as the author correctly points out. Any guidance you can provide will be much appreciated. Thank you." |
#5
|
|||
|
|||
A UDF for Counting Coloured Cells that are conditionally forma
Actually, Chip's approach is the one I'd kind of figured on taking myself and
I even got started on it, but why reinvent the wheel? g. Rather than trying to determine what conditional formatting has been applied by looking at the cell's displayed shading/font settings, determine if any conditional formatting has been applied at all. It makes for a slower operating function, but one that is much more maintenance free than to try to detect specific changes. You can quickly rule out the possiblity of any CF having been applied by simply testing for a .FormatCondition(1).Type and if there isn't one, then the cell doesn't have any (if it doesn't have 1, it can't have more than 1). I think Chip has even taken care of the extended number of conditional formats that can be applied in Excel 2007 with For Ndx = 1 To Rng.FormatConditions.Count of course the side effect of that is that if the user has defined lots and lots of conditional formats for lots and lots of cells, then the whole thing takes lots and lots longer to arrive at the results. Tradeoffs, there are always tradeoffs. "Dave Peterson" wrote: Check Chip Pearson's site: http://www.cpearson.com/Excel/CFColors.htm But it's not for the faint of heart. In fact, I'd use those mirror cells and then use them for the conditional formatting rules for the real cells (no sense updating both sets of formulas when something changes). ps. I think I've seen where xl2010 is going to have a .displayformat (or something like that) property that will make this easier. NeilG wrote: Hi All, I have been doing some extensive research and testing to determine whether there is any way that a UDF can be created that will return the count for cells on an input sheet that show a particular colour (as determined by CF). So far it would appear that this is not possible. I have accessed some of the most eminent MVPs and VBA gurus, but the conclusion would appear to be (and borne out by my testing) "No - can't be done" So this is my last attempt to find a solution - if it can't be done I can laboriously go throgh my 50 odd input sheets and mirror the CF conditions in a helper area and return a value of 1 each time the (complex) CF criteria is met - count therefore now possible; however, I would much prefer a UDF if possible. On a more general note this requirement seems to crop up regularly with developers, so I wonder if this is not something that MS should be addressing directly? Please find below the original question - and code blocks - that I put up. Any help would be much appreciated - even it just confirms that this is not possible. Many thanks. Neil "Does anyone have the robust code for a UDF that can be used to count the number of cells that conform to a certain (conditional) colour formatting (in Excel 2007) I found the following: Function CountColor(Color As Range, Range As Range) As Long Dim C As Range For Each C In Range If C.Interior.ColorIndex = Color.Interior.ColorIndex Then CountColor = CountColor + 1 End If Next End Function but this does not work consistently, though, of the 3 I did download, (1 from Ozgrid and 1 from Mr.Excel.com) this is the only one which did appear to function, albeit in limited conditions. I am using conditional formatting to draw the Users attention to invalid entries and ideally want to create a "one stop shop" report which the User can access easily to see if there are any invalid entries on any of the 50 plus input sheets i.e. without having to trawl through them individually..... The Ozgrid code is: Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean) Dim rCell As Range Dim lCol As Long Dim vResult '''''''''''''''''''''''''''''''''''''' 'Written by Ozgrid Business Applications 'www.ozgrid.com 'Sums or counts cells based on a specified fill color. 'You can now use the custom function (ColorFunction) like; '=ColorFunction($C$1,$A$1:$A$12,TRUE) to SUM the values in range of cells $A$1:$A$12 'that have the same fill color as cell $C$1. The reason it will SUM in this example is because 'we have used TRUE as the last argument for the custom function. 'To COUNT these cells that have the same fill color as cell $C$1 you could use: '=ColorFunction($C$1,$A$1:$A$12,FALSE) or =ColorFunction($C$1,$A$1:$A$12) by omitting the last argument 'our function will automatically default to using FALSE. 'Be aware that the changing of a cells fill color will not cause the Custom Function to recalculate, 'even if you press F9 (Recalculates the whole Workbook). You will need to either, select the cell 'and re-enter the formula, or go to EditReplace and replace = with =, or use Ctrl+Alt+F9 ''''''''''''''''''''''''''''''''''''''' lCol = rColor.Interior.ColorIndex If SUM = True Then For Each rCell In rRange If rCell.Interior.ColorIndex = lCol Then vResult = WorksheetFunction.SUM(rCell, vResult) End If Next rCell Else For Each rCell In rRange If rCell.Interior.ColorIndex = lCol Then vResult = 1 + vResult End If Next rCell End If ColorFunction = vResult End Function and, whilst this seemed to work partially, it doesn't if the cell formats change, which they will of course, and neither does it respond to F9 (re-calculate), as the author correctly points out. Any guidance you can provide will be much appreciated. Thank you." -- Dave Peterson . |
#6
|
|||
|
|||
A UDF for Counting Coloured Cells that are conditionally forma
Hi Neil,
If you just want to count the cells, you would probably be better off using the same formulas that you used for conditional formatting. If you would like some help using Chip's formulas. These may be of some help to you, but you will have to get help elsewhere, and anything that is different in Excel 2007 is not there. In particular look for Chip Pearson' and John McGimpsey on these pages: Conditional Formatting http://www.mvps.org/dmcritchie/excel/condfmt.htm Color Palette and the 56 Excel ColorIndex Colors http://www.mvps.org/dmcritchie/excel/colors.htm and of course you found the formulas on Chip Pearson's page. Color Functions In Excel http://www.cpearson.com/excel/colors.aspx In order to convert a complete spreadsheet into HTML everything was included in macro in Excel to HTML conversions http://www.mvps.org/dmcritchie/excel/xl2html.htm -- HTH, David McRitchie My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm "JLatham" wrote in message ... Actually, Chip's approach is the one I'd kind of figured on taking myself and I even got started on it, but why reinvent the wheel? g. Rather than trying to determine what conditional formatting has been applied by looking at the cell's displayed shading/font settings, determine if any conditional formatting has been applied at all. It makes for a slower operating function, but one that is much more maintenance free than to try to detect specific changes. You can quickly rule out the possiblity of any CF having been applied by simply testing for a .FormatCondition(1).Type and if there isn't one, then the cell doesn't have any (if it doesn't have 1, it can't have more than 1). I think Chip has even taken care of the extended number of conditional formats that can be applied in Excel 2007 with For Ndx = 1 To Rng.FormatConditions.Count of course the side effect of that is that if the user has defined lots and lots of conditional formats for lots and lots of cells, then the whole thing takes lots and lots longer to arrive at the results. Tradeoffs, there are always tradeoffs. "Dave Peterson" wrote: Check Chip Pearson's site: http://www.cpearson.com/Excel/CFColors.htm But it's not for the faint of heart. In fact, I'd use those mirror cells and then use them for the conditional formatting rules for the real cells (no sense updating both sets of formulas when something changes). ps. I think I've seen where xl2010 is going to have a .displayformat (or something like that) property that will make this easier. NeilG wrote: Hi All, I have been doing some extensive research and testing to determine whether there is any way that a UDF can be created that will return the count for cells on an input sheet that show a particular colour (as determined by CF). So far it would appear that this is not possible. I have accessed some of the most eminent MVPs and VBA gurus, but the conclusion would appear to be (and borne out by my testing) "No - can't be done" So this is my last attempt to find a solution - if it can't be done I can laboriously go throgh my 50 odd input sheets and mirror the CF conditions in a helper area and return a value of 1 each time the (complex) CF criteria is met - count therefore now possible; however, I would much prefer a UDF if possible. On a more general note this requirement seems to crop up regularly with developers, so I wonder if this is not something that MS should be addressing directly? Please find below the original question - and code blocks - that I put up. Any help would be much appreciated - even it just confirms that this is not possible. Many thanks. Neil "Does anyone have the robust code for a UDF that can be used to count the number of cells that conform to a certain (conditional) colour formatting (in Excel 2007) I found the following: Function CountColor(Color As Range, Range As Range) As Long Dim C As Range For Each C In Range If C.Interior.ColorIndex = Color.Interior.ColorIndex Then CountColor = CountColor + 1 End If Next End Function but this does not work consistently, though, of the 3 I did download, (1 from Ozgrid and 1 from Mr.Excel.com) this is the only one which did appear to function, albeit in limited conditions. I am using conditional formatting to draw the Users attention to invalid entries and ideally want to create a "one stop shop" report which the User can access easily to see if there are any invalid entries on any of the 50 plus input sheets i.e. without having to trawl through them individually..... The Ozgrid code is: Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean) Dim rCell As Range Dim lCol As Long Dim vResult '''''''''''''''''''''''''''''''''''''' 'Written by Ozgrid Business Applications 'www.ozgrid.com 'Sums or counts cells based on a specified fill color. 'You can now use the custom function (ColorFunction) like; '=ColorFunction($C$1,$A$1:$A$12,TRUE) to SUM the values in range of cells $A$1:$A$12 'that have the same fill color as cell $C$1. The reason it will SUM in this example is because 'we have used TRUE as the last argument for the custom function. 'To COUNT these cells that have the same fill color as cell $C$1 you could use: '=ColorFunction($C$1,$A$1:$A$12,FALSE) or =ColorFunction($C$1,$A$1:$A$12) by omitting the last argument 'our function will automatically default to using FALSE. 'Be aware that the changing of a cells fill color will not cause the Custom Function to recalculate, 'even if you press F9 (Recalculates the whole Workbook). You will need to either, select the cell 'and re-enter the formula, or go to EditReplace and replace = with =, or use Ctrl+Alt+F9 ''''''''''''''''''''''''''''''''''''''' lCol = rColor.Interior.ColorIndex If SUM = True Then For Each rCell In rRange If rCell.Interior.ColorIndex = lCol Then vResult = WorksheetFunction.SUM(rCell, vResult) End If Next rCell Else For Each rCell In rRange If rCell.Interior.ColorIndex = lCol Then vResult = 1 + vResult End If Next rCell End If ColorFunction = vResult End Function and, whilst this seemed to work partially, it doesn't if the cell formats change, which they will of course, and neither does it respond to F9 (re-calculate), as the author correctly points out. Any guidance you can provide will be much appreciated. Thank you." -- Dave Peterson . |
Thread Tools | |
Display Modes | |
|
|