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

mahmed1

Well-known Member
Joined
Mar 28, 2009
Messages
2,245
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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:

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
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

mahmed1

Well-known Member
Joined
Mar 28, 2009
Messages
2,245
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Any luck

thank you all
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,155
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
The only thing I can think of is to get rid of some unnecessary code lines...
Code:
Function SumColour(MatchColour As Range, MatchColourRange As Range) As Double
  Dim Cell As Range
  For Each Cell In MatchColourRange
    If Cell.Interior.Color = MatchColour.Interior.Color Then
      SumColour = SumColour + Cell.Value
    End If
  Next Cell
End Function
 

mahmed1

Well-known Member
Joined
Mar 28, 2009
Messages
2,245
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi Rick,

I thought the application.screen updating would speed it up? and the error part was just there encase the code had incurred an error (Ie cell value was incorrect) etc

I also tried to do it via the find method (I have been told this is the way better and quicker than loops)

but i am stuck with the what part as i am searching to match the colour which can not be found

The range to lookin is in B1:I200

This is my attempt

Code:
Function SumColour(MatchColour As Range, MatchColourRange As Range) As Double
Dim Found As Range
Dim FirstCell As String
Application.ScreenUpdating = False
Set Found = MatchColourRange.Find(What:=MatchColour.Interior.Color, _
                             After:=Range("B1"), _
                             SearchOrder:=xlByRows, _
                             SearchDirection:=xlPrevious)
If Found Is Nothing Then
Else
    FirstCell = Found.Address
    Do
       SumColour = SumColour + Found.Value
       Found = Found.FindNext(Found)
    Loop Until Found.Address <> FirstCell
End If
Application.ScreenUpdating = True
End Function
 
Last edited by a moderator:

mahmed1

Well-known Member
Joined
Mar 28, 2009
Messages
2,245
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

Hi Rick... I tried to use the findformat and set the searchformat to true but i cant seem to figure out how i could reference the MatchColour.Interior.color for the find format

My Attempt

Code:
Function SumColour(MatchColour As Range, MatchColourRange As Range) As Double
Dim Found As Range
Dim FirstCell As String
Application.ScreenUpdating = False
[U][B]'Application.FindFormat<<<MatchColour[/B][/U]
Set Found = MatchColourRange.Find(What:="", _
                             After:=Range("B1"), _
                             SearchOrder:=xlByRows, _
                             SearchDirection:=xlPrevious, _
                             [U][B]SearchFormat:=True) 'This is set to true[/B][/U]
If Found Is Nothing Then
Else
    FirstCell = Found.Address
    Do
       SumColour = SumColour + Found.Value
       Found = Found.FindNext(Found)
    Loop Until Found IS NOTHING and Found.Address = FirstCell
End If
Application.ScreenUpdating = True
End Function
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,155
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
I thought the application.screen updating would speed it up?
The only way turning off screen updating off speeds things up is when a lot of data is being written to the workbook... with screen updating off, the computer does not have to show you (on screen) each and every change that it makes one-at-a-time, rather, it makes the changes in memory and then shows you all the changes that were made all at once. With a UDF, only one cell is being changed, so turning screen updating off cannot help.



and the error part was just there encase the code had incurred an error (Ie cell value was incorrect) etc
What error do you think might happen? Do you have non-numbers in the cells that are colored? I assumed not which means no errors will occur which, in turn, means error trapping is not needed. If it were, though, there is no need to execute the On Error Return Next statement with each iteration of the loop... just put it before the loop... it will persist until turned off by the On Error GoTo 0 statement (which should be put after the Next statement).



I also tried to do it via the find method (I have been told this is the way better and quicker than loops)
The Find function will not work in a function used as a UDF inside a worksheet formula (that function would still work, however, if called by normal VB code). I cannot tell you why the Excel programmers decided to make that the case, especially since it used to work fine that way back in XL2003.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,118
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

Find will work in a UDF (since 2002); FindNext will not. I can't recall if you can use FindFormat though.

However, it would be much better to simply stop using colours as data. (I've lost count of how many times I've said that this week) If you apply the colours based on data, you can use that data in built-in functions like SUMIF without having to use UDFs that are slow and must be volatile - even then they won't recalculate when you change the colour of a cell.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,155
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
Find will work in a UDF (since 2002); FindNext will not. I can't recall if you can use FindFormat though.
You are right, of course, Find work fine for normal searches... it is FindFormat that apparently is blocked. Thanks for the correction.


However, it would be much better to simply stop using colours as data.
Agreed.
 

mahmed1

Well-known Member
Joined
Mar 28, 2009
Messages
2,245
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi Rick and Rory

thank you both for your help

unfortunately the spreadsheet is set up by colour and i needed to add by colour

i thought rather than having a function, have it is a macro that is called by the user (say refresh balance) to avoid it beung volatile and called all the time?

Now that i wont be using the function, can i have the find colour work now without looping ?

would you advise this is the best to do it and i also thought is it a good a idea to call this macro as soon as something changes within the matchlookup range? This way it will only call the macro if i manually call it or if someone inputs an amount in that cell
how can i do this via the find method?
thank you
 
Last edited by a moderator:

mahmed1

Well-known Member
Joined
Mar 28, 2009
Messages
2,245
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Im trying to do the same thing as the function but replace with a sub so its called but with the find method to make it more quicker
 

Forum statistics

Threads
1,136,445
Messages
5,675,900
Members
419,591
Latest member
mersanko

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top