?
Solved

Autosum colored excel cells

Posted on 2013-05-24
8
Medium Priority
?
526 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

752 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