?
Solved

Autosum colored excel cells

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Cancel future meetings from user mailboxes in Office 365 using Remove-CalendarEvents
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…
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…

649 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