• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Sum color returns #NAME?

Sanussha

New Member
Hi all,

I used below vba code to sum color but it returns #NAME? when i run the code. Could anyone advise me how to fix the error? Attached file for your reference.

>>> use code - tags <<<
Code:
Option Explicit

Function SumColor(MatchColor As Range) As Double

  Dim cell As Range
  Dim myColor As Long
  myColor = MatchColor.Cells(1, 1).Interior.Color

  For Each cell In sumRange
    If cell.Interior.Color = myColor Then
       SumColor = SumColor + cell.Value
    End If
  Next cell
End Function
And

*Also added on the workbook for any cell changes for below code:*

Code:
Private Sub Workbook_SelectionChange(ByVal Target As Range)

If Not Intersect(Target, Range("A:ZZ")) Is Nothing Then

End If

ActiveWorkbook.Calculate

End Sub
 

Attachments

Last edited by a moderator:

Sanussha

New Member
That's an xlsx file so there is no code in it, which is why you get the error.
Hi Debaser..tq for your reply..i have write the code in another macro workbook .xlsm and will run the code on any open workbook that i need..in this case i choose book1 as attached which is the result from my macro workbook..please check module 5 and 6 from my macro file as attached..sorry for confuse for not attaching macro file previously...appreciated for your assistance..
 

Attachments

Debaser

Well-Known Member
Since the code is in a different workbook, you need to call it with the name of the workbook too:

='INEOS PO Calculation Template.xlsm'!Sumcolor(C20:C21)

Note however that your function won't work because you haven't written it to take SumRange as an argument to it.
 

Sanussha

New Member
Hi Debaser..thanks i have added sumrange and the function is only work in that workbook only :confused:

May I know ='INEOS PO Calculation Template.xlsm'!Sumcolor(C20:C21) formula needed to be added at which place?

Because my macro debugging when i added them.

I try to added below code but it does not work.

>>> Sanussha <<<
>>>
use code - tags <<<
Code:
Dim sht As Worksheet
For Each sht In ActiveWorkbook.Worksheets
sht.Activate

' Thanks in advance ;)



Option Explicit

Function SumColor(MatchColor As Range, sumRange As Range) As Double

  Dim cell As Range
  Dim myColor As Long
  myColor = MatchColor.Cells(1, 1).Interior.Color

  For Each cell In sumRange
    If cell.Interior.Color = myColor Then
       SumColor = SumColor + cell.Value
    End If
  Next cell
End Function
 

Attachments

Last edited by a moderator:
Top