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

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

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

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

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

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

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

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