How to define a function that can sum all cells' value and then return the color of sum?

**bryansky** · 03-26-2013, 12:33 AM

I want to define a function that can sum all cells' value and then return the color of sum number
For example, A1:A10 is 1 to 10, the sum of A1:A10 is 55, and at the same time the interior color of another cell would change to the color that the colorindex

 Function iSumColor(r As Range, v As Range) As Double
Dim q As Range
    Dim result As Double
    result = 0
    For Each q In r
        
        result = result + q.Value
        
    Next

     Dim c As Range
     For Each c In q
     c.Interior.ColorIndex = result

    Next

   iSumColor = result

End Function

and I input the function "=iSumColor(A1:A10, B1)" in any cell, the cell should be 55, and the B1 cell's color should be color of index is 55, how do I do it?
what's wrong with my syntax?

**rylo** · 03-26-2013, 01:03 AM

Hi

Firstly

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code in [CODE] [/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

A function should only be used to return a result, not make a change to anything on the sheet. In this case, you are making a change to a cell format, so you should be using a sub.

Try the code below. When it runs, you will be asked to highligh a range. Select the range A1:A10, and continue.

Sub aaa()
  Dim Rng As Range
  Set Rng = Application.InputBox("Select range to action", Type:=8)
  
  holder = 0
  For Each ce In Rng
    holder = holder + ce.Value
    ce.Interior.ColorIndex = holder
  Next ce
End Sub

rylo

**bryansky** · 03-26-2013, 01:33 AM

Thanks very much

How to define a function that can sum all cells' value and then return the color of sum?

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

How to define a function that can sum all cells' value and then return the color of sum?

Re: How to define a function that can sum all cells' value and then return the color of su

Re: How to define a function that can sum all cells' value and then return the color of su

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions