How to track formatting changes

[Pflugs]

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

 

[Dave F]

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]

No, it doesn't. Excel doesn't recognize formatting changes as a cause to
recalculate (unfortunately).

Pflugs

 

[Dave F]

Does pressing F9 recalculate the cells?

 

[Dave Peterson]

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]

If you've found something that does recalculate when hitting F9, then why
wouldn't it calculate automatically? Even so, I'd be interested in the full
code of your function if you wouldn't mind.

Thanks,
pflugs

 

[Pflugs]

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

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.

 

[Dave F]

Why not include a button labled "Recalculate Now"?

That would seem pretty clear.

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]

Excel doesn't have any events that you could tie into (for just 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

 

[Pflugs]

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

Why not include a button labled "Recalculate Now"?

That would seem pretty clear.

 

[Dave Peterson]

There is (still) no event that fires when you change the formatting.

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