|
How to track formatting changes
I wrote a user-defined function that sums all cells in a given range that
have the same color fill as the calling cell. The code works great except when the user changes the fill color of a cell. The formula will not update because technically no values changed. How can I get around this? I am providing the code for others to use. ------------------------------------------------------------------------------------------ Function sumColor(range1 As Range, Optional range2 As Range, Optional range3 As Range, Optional range4 As Range) sumColor = 0 For Each cell In range1 If cell.Interior.ColorIndex = Application.ThisCell.Interior.ColorIndex Then sumColor = sumColor + cell.Value End If Next cell If Not range2 Is Nothing Then For Each cell In range2 If cell.Interior.ColorIndex = Application.ThisCell.Interior.ColorIndex Then sumColor = sumColor + cell.Value End If Next cell End If If Not range3 Is Nothing Then For Each cell In range3 If cell.Interior.ColorIndex = Application.ThisCell.Interior.ColorIndex Then sumColor = sumColor + cell.Value End If Next cell End If If Not range4 Is Nothing Then For Each cell In range4 If cell.Interior.ColorIndex = Application.ThisCell.Interior.ColorIndex Then sumColor = sumColor + cell.Value End If Next cell End If End Function ------------------------------------------------------------------------------------------ Thanks, Pflugs |
How to track formatting changes
I've used this code before to sum cells based on colors, and pressing F9 does
recalculate cells when their colors change: Function SumByColor(InRange As Range, WhatColorIndex As Integer, _ Optional OfText As Boolean = False) As Double ' ' This function return the SUM of the values of cells in ' InRange with a background color, or if OfText is True a ' font color, equal to WhatColorIndex. ' Dim Rng As Range Dim OK As Boolean Dave "Pflugs" wrote: No, it doesn't. Excel doesn't recognize formatting changes as a cause to recalculate (unfortunately). Pflugs "Dave F" wrote: Does pressing F9 recalculate the cells? "Pflugs" wrote: I wrote a user-defined function that sums all cells in a given range that have the same color fill as the calling cell. The code works great except when the user changes the fill color of a cell. The formula will not update because technically no values changed. How can I get around this? I am providing the code for others to use. ------------------------------------------------------------------------------------------ Function sumColor(range1 As Range, Optional range2 As Range, Optional range3 As Range, Optional range4 As Range) sumColor = 0 For Each cell In range1 If cell.Interior.ColorIndex = Application.ThisCell.Interior.ColorIndex Then sumColor = sumColor + cell.Value End If Next cell If Not range2 Is Nothing Then For Each cell In range2 If cell.Interior.ColorIndex = Application.ThisCell.Interior.ColorIndex Then sumColor = sumColor + cell.Value End If Next cell End If If Not range3 Is Nothing Then For Each cell In range3 If cell.Interior.ColorIndex = Application.ThisCell.Interior.ColorIndex Then sumColor = sumColor + cell.Value End If Next cell End If If Not range4 Is Nothing Then For Each cell In range4 If cell.Interior.ColorIndex = Application.ThisCell.Interior.ColorIndex Then sumColor = sumColor + cell.Value End If Next cell End If End Function ------------------------------------------------------------------------------------------ Thanks, Pflugs |
How to track formatting changes
No, it doesn't. Excel doesn't recognize formatting changes as a cause to
recalculate (unfortunately). Pflugs "Dave F" wrote: Does pressing F9 recalculate the cells? "Pflugs" wrote: I wrote a user-defined function that sums all cells in a given range that have the same color fill as the calling cell. The code works great except when the user changes the fill color of a cell. The formula will not update because technically no values changed. How can I get around this? I am providing the code for others to use. ------------------------------------------------------------------------------------------ Function sumColor(range1 As Range, Optional range2 As Range, Optional range3 As Range, Optional range4 As Range) sumColor = 0 For Each cell In range1 If cell.Interior.ColorIndex = Application.ThisCell.Interior.ColorIndex Then sumColor = sumColor + cell.Value End If Next cell If Not range2 Is Nothing Then For Each cell In range2 If cell.Interior.ColorIndex = Application.ThisCell.Interior.ColorIndex Then sumColor = sumColor + cell.Value End If Next cell End If If Not range3 Is Nothing Then For Each cell In range3 If cell.Interior.ColorIndex = Application.ThisCell.Interior.ColorIndex Then sumColor = sumColor + cell.Value End If Next cell End If If Not range4 Is Nothing Then For Each cell In range4 If cell.Interior.ColorIndex = Application.ThisCell.Interior.ColorIndex Then sumColor = sumColor + cell.Value End If Next cell End If End Function ------------------------------------------------------------------------------------------ Thanks, Pflugs |
How to track formatting changes
Does pressing F9 recalculate the cells?
"Pflugs" wrote: I wrote a user-defined function that sums all cells in a given range that have the same color fill as the calling cell. The code works great except when the user changes the fill color of a cell. The formula will not update because technically no values changed. How can I get around this? I am providing the code for others to use. ------------------------------------------------------------------------------------------ Function sumColor(range1 As Range, Optional range2 As Range, Optional range3 As Range, Optional range4 As Range) sumColor = 0 For Each cell In range1 If cell.Interior.ColorIndex = Application.ThisCell.Interior.ColorIndex Then sumColor = sumColor + cell.Value End If Next cell If Not range2 Is Nothing Then For Each cell In range2 If cell.Interior.ColorIndex = Application.ThisCell.Interior.ColorIndex Then sumColor = sumColor + cell.Value End If Next cell End If If Not range3 Is Nothing Then For Each cell In range3 If cell.Interior.ColorIndex = Application.ThisCell.Interior.ColorIndex Then sumColor = sumColor + cell.Value End If Next cell End If If Not range4 Is Nothing Then For Each cell In range4 If cell.Interior.ColorIndex = Application.ThisCell.Interior.ColorIndex Then sumColor = sumColor + cell.Value End If Next cell End If End Function ------------------------------------------------------------------------------------------ Thanks, Pflugs |
How to track formatting changes
I think it's a training issue for the user. You'll have to make them understand
that they have to recalculate before they can trust the answer. Pflugs wrote: I wrote a user-defined function that sums all cells in a given range that have the same color fill as the calling cell. The code works great except when the user changes the fill color of a cell. The formula will not update because technically no values changed. How can I get around this? I am providing the code for others to use. ------------------------------------------------------------------------------------------ Function sumColor(range1 As Range, Optional range2 As Range, Optional range3 As Range, Optional range4 As Range) sumColor = 0 For Each cell In range1 If cell.Interior.ColorIndex = Application.ThisCell.Interior.ColorIndex Then sumColor = sumColor + cell.Value End If Next cell If Not range2 Is Nothing Then For Each cell In range2 If cell.Interior.ColorIndex = Application.ThisCell.Interior.ColorIndex Then sumColor = sumColor + cell.Value End If Next cell End If If Not range3 Is Nothing Then For Each cell In range3 If cell.Interior.ColorIndex = Application.ThisCell.Interior.ColorIndex Then sumColor = sumColor + cell.Value End If Next cell End If If Not range4 Is Nothing Then For Each cell In range4 If cell.Interior.ColorIndex = Application.ThisCell.Interior.ColorIndex Then sumColor = sumColor + cell.Value End If Next cell End If End Function ------------------------------------------------------------------------------------------ Thanks, Pflugs -- Dave Peterson |
How to track formatting changes
I would rather do the thinking for the user. Most people I work with don't
know about F9 anyway. Also, hitting F9 or recalculating doesn't solve the problem because Excel doesn't flag that calling cell as one to be recalculated. However, I tried a SelectionChange event with "Application.CalculateFullRebuild", and that did work. The user must still select something different for it to work, but the likelihood of that is high. The downside is that the function now requires an Event and a FullRebuild after each selection. I would like to keep it at only a function (a module), but you obviously can't call a Calculation from within a function. Is there any way to force recalculation on formatting changes? Ideas? Thanks, Pflugs "Dave Peterson" wrote: I think it's a training issue for the user. You'll have to make them understand that they have to recalculate before they can trust the answer. Pflugs wrote: I wrote a user-defined function that sums all cells in a given range that have the same color fill as the calling cell. The code works great except when the user changes the fill color of a cell. The formula will not update because technically no values changed. How can I get around this? I am providing the code for others to use. ------------------------------------------------------------------------------------------ Function sumColor(range1 As Range, Optional range2 As Range, Optional range3 As Range, Optional range4 As Range) sumColor = 0 For Each cell In range1 If cell.Interior.ColorIndex = Application.ThisCell.Interior.ColorIndex Then sumColor = sumColor + cell.Value End If Next cell If Not range2 Is Nothing Then For Each cell In range2 If cell.Interior.ColorIndex = Application.ThisCell.Interior.ColorIndex Then sumColor = sumColor + cell.Value End If Next cell End If If Not range3 Is Nothing Then For Each cell In range3 If cell.Interior.ColorIndex = Application.ThisCell.Interior.ColorIndex Then sumColor = sumColor + cell.Value End If Next cell End If If Not range4 Is Nothing Then For Each cell In range4 If cell.Interior.ColorIndex = Application.ThisCell.Interior.ColorIndex Then sumColor = sumColor + cell.Value End If Next cell End If End Function ------------------------------------------------------------------------------------------ Thanks, Pflugs -- Dave Peterson |
How to track formatting changes
Why not include a button labled "Recalculate Now"?
That would seem pretty clear. "Pflugs" wrote: I would rather do the thinking for the user. Most people I work with don't know about F9 anyway. Also, hitting F9 or recalculating doesn't solve the problem because Excel doesn't flag that calling cell as one to be recalculated. However, I tried a SelectionChange event with "Application.CalculateFullRebuild", and that did work. The user must still select something different for it to work, but the likelihood of that is high. The downside is that the function now requires an Event and a FullRebuild after each selection. I would like to keep it at only a function (a module), but you obviously can't call a Calculation from within a function. Is there any way to force recalculation on formatting changes? Ideas? Thanks, Pflugs "Dave Peterson" wrote: I think it's a training issue for the user. You'll have to make them understand that they have to recalculate before they can trust the answer. Pflugs wrote: I wrote a user-defined function that sums all cells in a given range that have the same color fill as the calling cell. The code works great except when the user changes the fill color of a cell. The formula will not update because technically no values changed. How can I get around this? I am providing the code for others to use. ------------------------------------------------------------------------------------------ Function sumColor(range1 As Range, Optional range2 As Range, Optional range3 As Range, Optional range4 As Range) sumColor = 0 For Each cell In range1 If cell.Interior.ColorIndex = Application.ThisCell.Interior.ColorIndex Then sumColor = sumColor + cell.Value End If Next cell If Not range2 Is Nothing Then For Each cell In range2 If cell.Interior.ColorIndex = Application.ThisCell.Interior.ColorIndex Then sumColor = sumColor + cell.Value End If Next cell End If If Not range3 Is Nothing Then For Each cell In range3 If cell.Interior.ColorIndex = Application.ThisCell.Interior.ColorIndex Then sumColor = sumColor + cell.Value End If Next cell End If If Not range4 Is Nothing Then For Each cell In range4 If cell.Interior.ColorIndex = Application.ThisCell.Interior.ColorIndex Then sumColor = sumColor + cell.Value End If Next cell End If End Function ------------------------------------------------------------------------------------------ Thanks, Pflugs -- Dave Peterson |
How to track formatting changes
Excel doesn't have any events that you could tie into (for just formatting
changes). Pflugs wrote: I would rather do the thinking for the user. Most people I work with don't know about F9 anyway. Also, hitting F9 or recalculating doesn't solve the problem because Excel doesn't flag that calling cell as one to be recalculated. However, I tried a SelectionChange event with "Application.CalculateFullRebuild", and that did work. The user must still select something different for it to work, but the likelihood of that is high. The downside is that the function now requires an Event and a FullRebuild after each selection. I would like to keep it at only a function (a module), but you obviously can't call a Calculation from within a function. Is there any way to force recalculation on formatting changes? Ideas? Thanks, Pflugs "Dave Peterson" wrote: I think it's a training issue for the user. You'll have to make them understand that they have to recalculate before they can trust the answer. Pflugs wrote: I wrote a user-defined function that sums all cells in a given range that have the same color fill as the calling cell. The code works great except when the user changes the fill color of a cell. The formula will not update because technically no values changed. How can I get around this? I am providing the code for others to use. ------------------------------------------------------------------------------------------ Function sumColor(range1 As Range, Optional range2 As Range, Optional range3 As Range, Optional range4 As Range) sumColor = 0 For Each cell In range1 If cell.Interior.ColorIndex = Application.ThisCell.Interior.ColorIndex Then sumColor = sumColor + cell.Value End If Next cell If Not range2 Is Nothing Then For Each cell In range2 If cell.Interior.ColorIndex = Application.ThisCell.Interior.ColorIndex Then sumColor = sumColor + cell.Value End If Next cell End If If Not range3 Is Nothing Then For Each cell In range3 If cell.Interior.ColorIndex = Application.ThisCell.Interior.ColorIndex Then sumColor = sumColor + cell.Value End If Next cell End If If Not range4 Is Nothing Then For Each cell In range4 If cell.Interior.ColorIndex = Application.ThisCell.Interior.ColorIndex Then sumColor = sumColor + cell.Value End If Next cell End If End Function ------------------------------------------------------------------------------------------ Thanks, Pflugs -- Dave Peterson -- Dave Peterson |
How to track formatting changes
Again, because that would require a "CalculateFull" or
"CalculateFullRebuild," and that could be computationally costly for some spreadsheets. Also, the button would have to be automatically created and placed. I think that seeing as this function requires either events, buttons, or specific user actions (besides just entering the formula or installing an add-in), it's not worth pursuing. There's just too much that the average user. That is, unless you know of a way to make it update without an event, as you alluded to a few posts ago. I wasn't able to make out the body of the function from the header. Thanks anyways, Pflugs "Dave F" wrote: Why not include a button labled "Recalculate Now"? That would seem pretty clear. "Pflugs" wrote: I would rather do the thinking for the user. Most people I work with don't know about F9 anyway. Also, hitting F9 or recalculating doesn't solve the problem because Excel doesn't flag that calling cell as one to be recalculated. However, I tried a SelectionChange event with "Application.CalculateFullRebuild", and that did work. The user must still select something different for it to work, but the likelihood of that is high. The downside is that the function now requires an Event and a FullRebuild after each selection. I would like to keep it at only a function (a module), but you obviously can't call a Calculation from within a function. Is there any way to force recalculation on formatting changes? Ideas? Thanks, Pflugs "Dave Peterson" wrote: I think it's a training issue for the user. You'll have to make them understand that they have to recalculate before they can trust the answer. Pflugs wrote: I wrote a user-defined function that sums all cells in a given range that have the same color fill as the calling cell. The code works great except when the user changes the fill color of a cell. The formula will not update because technically no values changed. How can I get around this? I am providing the code for others to use. ------------------------------------------------------------------------------------------ Function sumColor(range1 As Range, Optional range2 As Range, Optional range3 As Range, Optional range4 As Range) sumColor = 0 For Each cell In range1 If cell.Interior.ColorIndex = Application.ThisCell.Interior.ColorIndex Then sumColor = sumColor + cell.Value End If Next cell If Not range2 Is Nothing Then For Each cell In range2 If cell.Interior.ColorIndex = Application.ThisCell.Interior.ColorIndex Then sumColor = sumColor + cell.Value End If Next cell End If If Not range3 Is Nothing Then For Each cell In range3 If cell.Interior.ColorIndex = Application.ThisCell.Interior.ColorIndex Then sumColor = sumColor + cell.Value End If Next cell End If If Not range4 Is Nothing Then For Each cell In range4 If cell.Interior.ColorIndex = Application.ThisCell.Interior.ColorIndex Then sumColor = sumColor + cell.Value End If Next cell End If End Function ------------------------------------------------------------------------------------------ Thanks, Pflugs -- Dave Peterson |
How to track formatting changes
There is (still) no event that fires when you change the formatting.
Pflugs wrote: Again, because that would require a "CalculateFull" or "CalculateFullRebuild," and that could be computationally costly for some spreadsheets. Also, the button would have to be automatically created and placed. I think that seeing as this function requires either events, buttons, or specific user actions (besides just entering the formula or installing an add-in), it's not worth pursuing. There's just too much that the average user. That is, unless you know of a way to make it update without an event, as you alluded to a few posts ago. I wasn't able to make out the body of the function from the header. Thanks anyways, Pflugs "Dave F" wrote: Why not include a button labled "Recalculate Now"? That would seem pretty clear. "Pflugs" wrote: I would rather do the thinking for the user. Most people I work with don't know about F9 anyway. Also, hitting F9 or recalculating doesn't solve the problem because Excel doesn't flag that calling cell as one to be recalculated. However, I tried a SelectionChange event with "Application.CalculateFullRebuild", and that did work. The user must still select something different for it to work, but the likelihood of that is high. The downside is that the function now requires an Event and a FullRebuild after each selection. I would like to keep it at only a function (a module), but you obviously can't call a Calculation from within a function. Is there any way to force recalculation on formatting changes? Ideas? Thanks, Pflugs "Dave Peterson" wrote: I think it's a training issue for the user. You'll have to make them understand that they have to recalculate before they can trust the answer. Pflugs wrote: I wrote a user-defined function that sums all cells in a given range that have the same color fill as the calling cell. The code works great except when the user changes the fill color of a cell. The formula will not update because technically no values changed. How can I get around this? I am providing the code for others to use. ------------------------------------------------------------------------------------------ Function sumColor(range1 As Range, Optional range2 As Range, Optional range3 As Range, Optional range4 As Range) sumColor = 0 For Each cell In range1 If cell.Interior.ColorIndex = Application.ThisCell.Interior.ColorIndex Then sumColor = sumColor + cell.Value End If Next cell If Not range2 Is Nothing Then For Each cell In range2 If cell.Interior.ColorIndex = Application.ThisCell.Interior.ColorIndex Then sumColor = sumColor + cell.Value End If Next cell End If If Not range3 Is Nothing Then For Each cell In range3 If cell.Interior.ColorIndex = Application.ThisCell.Interior.ColorIndex Then sumColor = sumColor + cell.Value End If Next cell End If If Not range4 Is Nothing Then For Each cell In range4 If cell.Interior.ColorIndex = Application.ThisCell.Interior.ColorIndex Then sumColor = sumColor + cell.Value End If Next cell End If End Function ------------------------------------------------------------------------------------------ Thanks, Pflugs -- Dave Peterson -- Dave Peterson |
| All times are GMT +1. The time now is 11:13 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com