?
Solved

Autosum colored excel cells

Posted on 2013-05-24
8
Medium Priority
?
596 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
5 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 81

Accepted Solution

by:
byundt earned 2000 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 81

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

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Lost Word File? Eagerly, need it back? Read ahead; this File Recovery guide is for you.
I came across an unsolved Outlook issue and here is my solution.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

850 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