Solved

Autosum colored excel cells

Posted on 2013-05-24
8
472 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 81

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 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

863 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

19 Experts available now in Live!

Get 1:1 Help Now