Can i quicken this Function or use diff code to speed it Up

[mahmed1]

Hi

I have this function that is used on several sheets and the The range to lookin is in B2:I200 therefore it loops several of times when this function is called or when a cell changes within that range on that sheet

I double on a cell to change its colour and add a value and then this function is called to sum the colour

This works fine but can be clunky at times

Need help speeding it up or using different method

Here is the working Code:

Function SumColour(MatchColour As Range, MatchColourRange) As Double
Dim Cell As Range
Application.ScreenUpdating = False
For Each Cell In MatchColourRange
    If Cell.Interior.Color = MatchColour.Interior.Color Then
        On Error Resume Next
        SumColour = SumColour + Cell.Value
        On Error GoTo 0
    End If
Next Cell
Application.ScreenUpdating = True
End Function

 

[RoryA]

Either the name is wrong, or you need to specify which workbook.

 

[mahmed1]

Hi

the sheet names are definately correct and the workbook i an running code is the workbook with the sheets

 

[Rick Rothstein]

the sheet names are definately correct...

When you say that, your sheet names (on the worksheet's tabs) definitely say Sheet1, Sheet2, Sheet3 and Sheet4 because that is what this line of code expects them to be... the actual name on the tab, not the generic sheet position (code) name...

SheetNames = Array("Sheet1", "Sheet2", "Sheet3", "Sheet4")

 

[mahmed1]

Hi have

sheet tab names sheet1, then a sheetname called index which im ignoring and then sheet2,sheet3

 

[Rick Rothstein]

Hi have

sheet tab names sheet1, then a sheetname called index which im ignoring and then sheet2,sheet3

Is there a Sheet4 like you said there would be in Message #12 (if not, are there 3 or 4 sheets being processed)?

 

[mahmed1]

Hi there are sheets (sheet1 to sheet4)
i have many other sheets in my workbook but those are the 4sheete that will be worked/processed on

 

[mahmed1]

Any luck rick?

i suppose i could do a for each loop to say if the sheet name is not 1 of the sheets then skip over it but still curious to why your code wont work. Based on what rory has said, would i need to qualify as thisworkbook.worksheets(SN)?

 

[Rick Rothstein]

Any luck rick?

i suppose i could do a for each loop to say if the sheet name is not 1 of the sheets then skip over it but still curious to why your code wont work. Based on what rory has said, would i need to qualify as thisworkbook.worksheets(SN)?

I am at a loss what to tell you. The only way this line of code...

Set WS = Worksheets(SN)

could fail with a subscript out of range error is if the text assigned to SN is not the name on any of your tabs. Can you send me a copy of the workbook that my code does this on? Maybe I'll be able to spot the problem by watching the code work, step-by-step, up to the point where it fails. My email address is...

rick DOT news AT verizon DOT net

 

[mahmed1]

Thank you, il send you the workbook

thanks

 

[mahmed1]

Hi Rick

Silly Me

The sheet had a double space in there which i did not realise until i tested the length of the tab name

thank you, you have been amazing