Faster way to loop

[BRB1983]

I would like to know if there is a faster way for a loop to run. Here is what i have now and it is slow.

  Dim rng As Range    Dim lColorCounter As Long
    Dim rngCell As Range
    Set rng = Sheets("MASTER LINE LIST").Range("C2:Z2000")
    lColorCounter = 0
        For Each rngCell In rng
        'Checking BLUE color
            If Cells(rngCell.Row, rngCell.Column).DisplayFormat.Interior.Color = RGB(0, 176, 240) Then
                lColorCounter = lColorCounter + 1
            End If
        Next
    Sheets("Status").Range("C5") = lColorCounter
    lColorCounter = 0
        For Each rngCell In rng
        'Checking Yellor color
            If Cells(rngCell.Row, rngCell.Column).DisplayFormat.Interior.Color = RGB(255, 255, 0) Then
                lColorCounter = lColorCounter + 1
            End If
        Next
    Sheets("Status").Range("B5") = lColorCounter

 

[Peter_SSs]

If supported by your Excel version, instead of looping, have a look at using autofilter by color & then count the visible cells.

That is one of the options I had in mind but there are 24 columns & 2 colours. My testing is that if the colours can be in any of those 24 columns then such a method is considerably slower than that suggested in post 4. Never-the-less that is partly why I asked especially Q3 in post 7.

 

[Fazza]

24 columns! I hadn't read the question carefully enough to see that, Peter.
Sounds like a helper column or two could really make it much more efficient.

 

[Peter_SSs]

Sounds like a helper column or two could really make it much more efficient.

Possibly, but the question is about speed & I'm not sure that will be faster either. In any case, I'm only pursuing it out of interest as the existing suggestions only take a fraction of a second so any improvement will be non-consequential unless the process is to be repeated a large number of times.

 

[Fazza]

I was thinking faster in having the helper columns doing the work. Populated directly with a count. A SUM formula at the bottom of the helper column and all that the code - if needed at all - would need to to would be to read the single .value2 from a cell or defined name. Barely take an instant?

 

[BRB1983]

yes, both blue and yellow can occur in any of the cells in the 24 columns. Not all Cells in Range C2:Z**** are used. if we could use "UsedRange" that would work as well i think.
yes, rows and columns will have both colors but eventually all yellow.
colors are there by conditional formatting, using match-format Fill (=MATCH(C2,Final!$A$1:$A$6000,0)).
Only about 4500 cells will eventually contain color.

 

[BRB1983]

yellow = (=MATCH(C2,Final!$A$1:$A$6000,0)
blue = (=MATCH(C2,Circulation!$A$1:$A$6000,0)

 

[igold]

Out of curiosity, about how long does it take for whatever macro you are using now to complete. Is it minutes or seconds...

 

[BRB1983]

About 1 min. give or take

 

[igold]

Do you have a lot of other stuff going on with that machine. I only ask because on my stand alone computer, my test data which uses the range C2:Z10000, the code executes in less than ½ second.

 

[BRB1983]

no i don't. my work partner and i are the only one using this and his unit takes about the same time. his unit is approximately. 4 months old.