• 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

  • Book 1.xlsx
    44 KB · Views: 0
Last edited by a moderator:
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

  • Book 1.xlsx
    44 KB · Views: 4
  • INEOS PO Calculation Template.xlsm
    53.1 KB · Views: 3
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.
 
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

  • Book 1.xlsx
    44 KB · Views: 1
  • INEOS PO Calculation Template.xlsm
    53.4 KB · Views: 3
Last edited by a moderator:
Hi Debaser, thanks a lot it worked but only on 1 cell it shows #VALUE?. Do you know why and how to fix that?
 

Attachments

  • Book 1 .xlsx
    45.1 KB · Views: 1
  • INEOS PO Calculation Template.xlsm
    53.4 KB · Views: 1
Back
Top