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

 

[igold]

Something does not sound right. There has to be something going on for this small piece of code to take a minute to execute. Is it just this one task that takes long or do you notice sluggishness on all apps...

 

[BRB1983]

just that one

 

[BRB1983]

i have 26 different codes in this workbook, total of 48 sheets, the code that we are discussing is pasted in sheet 47, not on module.
some codes take longer than others depending on what its doing. but they are not slow for the most part.
the conditional formatting is set on sheet43 "master line list" (=MATCH(C2,Circulation!$A$1:$A$6000,0)) & (=MATCH(C2,Final!$A$1:$A$6000,0))
matching between sheets 43, 46 "Circulation", & 45 "Final" .. When match between master line list & circulation cell turns blue and when match between master line list & Final, cell turns yellow. Example Cell ("D:13") has a flange number "2200" if flange number 2200 match file name listed in circulation sheet then it turns blue. then after a few days to a week at most, flange 2200 will match file name listed in final sheet, and tun from blue to yellow. So each used cell in range C2:Z2000 will go from no color, to blue, then eventually to yellow, from conditional formatting.

 

[Peter_SSs]

Thanks for the additional information. It does allow for a much faster count.

With my sample data which has data in every cell in C2:Z2000 of Master Line List which yields a total of about 4,700 matches in the other 2 sheets, I get these results for times.

Post 2: 10.6 seconds
Post 4 (with the following adjustment): 5.4 seconds

rgb = rngCell.DisplayFormat.Interior.Color

Code below: 0.07 seconds (so considerably faster )

See how it goes in your actual workbook.

Sub CountBlueYellow()
  Dim db As Object, dy As Object
  Dim a As Variant
  Dim i As Long, j As Long, uba2 As Long, lr As Long, lc As Long, lbluecounter As Long, lyellowcounter As Long
  
  Set dy = CreateObject("Scripting.Dictionary")
  Set db = CreateObject("Scripting.Dictionary")
  With Sheets("Circulation")
    a = .Range("A2", .Range("A" & .Rows.Count).End(xlUp)).Value
    For i = 1 To UBound(a)
      db(a(i, 1)) = Empty
    Next i
  End With
    With Sheets("final")
    a = .Range("A2", .Range("A" & .Rows.Count).End(xlUp)).Value
    For i = 1 To UBound(a)
      dy(a(i, 1)) = Empty
    Next i
  End With
  With Sheets("MASTER LINE LIST")
    lr = .Columns("C:Z").Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    lc = .Rows("2:2000").Find(What:="*", LookIn:=xlFormulas, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
    a = .Range("C2").Resize(lr - 1, lc - 2).Value
  End With
  uba2 = UBound(a, 2)
  For i = 1 To UBound(a)
    For j = 1 To uba2
      If Not IsEmpty(a(i, j)) Then
        If db.exists(a(i, j)) Then
          lbluecounter = lbluecounter + 1
        ElseIf dy.exists(a(i, j)) Then
          lyellowcounter = lyellowcounter + 1
        End If
      End If
    Next j
  Next i
  With Sheets("Status")
    .Range("C5").Value = lbluecounter
    .Range("B5").Value = lyellowcounter
  End With
End Sub

 

[igold]

@Peter_SSs


Perhaps you did not see on my post 19 that I ran my code on 10K rows of data. If I cut that range back down to 2k of data, then my code runs in 0.1094 seconds. A barely perceptible difference from 0.07 seconds. The only unknown is the amount of computing power we both are using.

making this change

rgb = rngCell[COLOR=#0000FF].DisplayFormat[/COLOR].Interior.Color

adds a 1/10 second to my above time. What is the difference as far as results goes between the two lines of code.

I tried to time your code on my machine, but I am unclear as to what data is on your "Circulation" and "final" sheets.

 

[igold]

My timings comparison are not correct as you ran my code on your data with your machine. Sorry about my idiocy, I obviously need another Vodka!

I am still curious though, about the difference in the line of changed code I asked about.

 

[Peter_SSs]

What is the difference as far as results goes between the two lines of code.

The colour in the OP's cells are there by Conditional Formatting, not direct application. Without using DisplayFormat your code would not pick up the colour applied by the CF.

For your tests, have you actually applied the CF as advised by the OP?

My sample data was constructed as follows.
Circulation: A2:A500 holds 5000, 5001, 5002, ...
Final: A2:A500 holds 1, 2, 3, ...

Master Line List: C2:Z2000
- are random numbers from 1 to 9999
- has both CF rules as given applied to that range.
Here is a small section of that sheet:

<b>MASTER LINE LIST</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:8pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:56px;" /><col style="width:56px;" /><col style="width:56px;" /><col style="width:56px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >C</td><td >D</td><td >E</td><td >F</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-size:10pt; text-align:right; ">9255</td><td style="font-size:10pt; text-align:right; ">8933</td><td style="background-color:#ffff00; font-size:10pt; text-align:right; ">19</td><td style="font-size:10pt; text-align:right; ">8282</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="background-color:#ffff00; font-size:10pt; text-align:right; ">316</td><td style="background-color:#00b0f0; font-size:10pt; text-align:right; ">5028</td><td style="font-size:10pt; text-align:right; ">8920</td><td style="background-color:#00b0f0; font-size:10pt; text-align:right; ">5031</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="font-size:10pt; text-align:right; ">3411</td><td style="font-size:10pt; text-align:right; ">2046</td><td style="font-size:10pt; text-align:right; ">3955</td><td style="font-size:10pt; text-align:right; ">6660</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="font-size:10pt; text-align:right; ">6720</td><td style="background-color:#ffff00; font-size:10pt; text-align:right; ">452</td><td style="font-size:10pt; text-align:right; ">8731</td><td style="font-size:10pt; text-align:right; ">7344</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="background-color:#ffff00; font-size:10pt; text-align:right; ">455</td><td style="font-size:10pt; text-align:right; ">4616</td><td style="background-color:#00b0f0; font-size:10pt; text-align:right; ">5140</td><td style="font-size:10pt; text-align:right; ">6415</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; background-color:#fffcf9; border-style: groove ;border-color:#ff0000"><tr><td ><b>Conditional formatting </b></td></tr><tr><td ><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial; font-size:10pt; padding-left:2pt; padding-right:2pt; "><tr><td >Cell</td><td >Nr.: / Condition</td><td >Format</td></tr><tr><td >C2</td><td >1. / Formula is =MATCH(C2,Final!$A$1:$A$6000,0)</td><td style="background-color:#ffff00; ">Abc</td></tr><tr><td >C2</td><td >2. / Formula is =MATCH(C2,Circulation!$A$1:$A$6000,0)</td><td style="background-color:#00b0f0; ">Abc</td></tr></table></td></tr></table>

 

[igold]

Thanks for this. I will try to duplicate your work. Any chance of throwing your workbook up to a site like dropbox or something similar. That would only be easier for me and not for you. LOL.

 

[Peter_SSs]

Any chance of throwing your workbook up to a site like dropbox or something similar.

Sure, try here
https://www.dropbox.com/s/pe2ou7k3bd714pf/BRB1983%20count%20coloured%20cells.xlsm?dl=0

 

[igold]

Thank you very much.

Here are my timing results taken from your Status tab...

John_w			2.109 secs
igold			1.078 secs
Peter			0.063 secs

<colgroup><col span="3"><col></colgroup><tbody>
</tbody>

<colgroup><col span="3"><col></colgroup><tbody>
</tbody>

I do still think that there is something amiss with the machine the OP is using.