Solved

Autosum colored excel cells

Posted on 2013-05-24
8
466 Views
Last Modified: 2014-05-23
Hi, we have an excel addin (below) that gives allows us to sum a cell based on the color of the cell, example: if I selected cells A1-A20 and only Cells A5-A8 background color is red, and I go to Cell B9 and do a colorsum=(a5:a8) and change B9 to background red, it would give me give me the sum of A5:a8

Function ColorSum(RNG As Range) As Double

Dim aValue As Double
Dim aColor As Double
Dim aCell As String

Application.Volatile

aCell = Application.Caller.Address
aColor = Range(aCell).Interior.Color

For Each arng In RNG
    If arng.Interior.Color = aColor Then
        aValue = aValue + arng.Value
    End If
Next arng

ColorSum = aValue

End Function

__________________________

Problem is that I have to keep hitting F9 to refresh to recalculate to show the correct number in B9, any way to make it automatic everytime I change a color, or add a number, or add a cell, or whatever?

UPDATE: So when I highlight a cell and change the color to red and THEN put the number in, B9 updates automatically....but if there is a cell with a number in it and I fill it to red, B9 doesn't update unless you hit F9

Frustrating

Thanks
0
Comment
Question by:dealstrike
  • 2
  • 2
8 Comments
 

Expert Comment

by:murphyins
ID: 39195473
I do not have a solution for this problem, but wanted to let you know that you should change the topic category to primarily be Microsoft Excel Spreadsheet instead of Cisco Pix Firewall. It will take much longer to get a response if you leave it as is.
0
 

Author Comment

by:dealstrike
ID: 39195485
Oh thanks....not sure how to remove it from the Topics...will Request attention
0
 
LVL 80

Accepted Solution

by:
byundt earned 500 total points
ID: 39195658
You're not going to like hearing this, but what you are observing is a "feature" and there's not much that can be done about it.

I could offer a macro to force recalculation whenever the user selects a different cell, but that imposes high overhead.
'This sub must go in the code pane for the worksheet being watched. It won't work at all if installed anywhere else!
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range("B9").Calculate
End Sub

Open in new window

0
 

Author Comment

by:dealstrike
ID: 39195672
What do you mean high overhead? What's the macro?

Thanks
0
 
LVL 80

Expert Comment

by:byundt
ID: 39195686
Every time the user (or another macro) selects a different cell on the worksheet, the macro runs. Seems like high overhead to me, but if you have a small workbook that recalculates instantly, it isn't a big deal. Try the macro--you'll quickly find out if it is a real problem or just an imaginary one. :-)

To install the macro, rightclick the sheet tab and choose View Code. Paste it in the code pane that you see as a result. ALT + F11 to return to the worksheet user interface.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

This article will show you how to use shortcut menus in the Access run-time environment.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now