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

 

[John_w]

This should be slightly faster.

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

 

[BRB1983]

yes that helps thanks!

 

[igold]

This should be even quicker...

    Dim rng As Range
    Dim lColorCounter1 As Long, lColorCounter2 As Long
    Dim rgb As Long
    Dim rngCell As Range
    
    Set rng = Sheets("MASTER LINE LIST").Range("C2:Z2000")
    lColorCounter1 = 0
    lColorCounter2 = 0
    For Each rngCell In rng
        rgb = rngCell.Interior.Color
        Select Case rgb
            Case Is = 15773696
                lColorCounter1 = lColorCounter1 + 1
            Case Is = 65535
                lColorCounter2 = lColorCounter2 + 1
        End Select
    Next
    Sheets("Status").Range("C5") = lColorCounter1
    Sheets("Status").Range("B5") = lColorCounter2

 

[Peter_SSs]

Just checking a couple of other things that may impact speed.

1. Are the colours in the range applied by Conditional Formatting, not by CF, or a mixture?
If CF is involved, what can you tell us about the CF rule(s) that have applied these 2 colours?

2. Are you able to have any idea of about how many of the nearly 48,000 cells will have either of these 2 colours?

 

[igold]

@Peter_SSs

I would like to understand what would you do with the answers from your questions (if you received perfect information), that would enable to you to process this loop faster...

Thanks in advance for your time.

igold

 

[Peter_SSs]

I would like to understand what would you do with the answers from your questions (if you received perfect information), that would enable to you to process this loop faster...

Thanks in advance for your time.

igold

There are lots of combinations of answers to my questions possible. Many/most of those combinations would probably leave me thinking that your code will be as good as it gets. However, it is just possible that there my be faster ways but rather than detail several ways now that may eventuate, I would rather wait for the actual answers.

I have further questions for BRB1983:

3. Is it true that both the blue and yellow cells can occur in any of the 24 columns involved or was your code checking every cell/column because the loop was easy to set up like that? If there are any of the 24 columns that the blue &/or yellow cannot occur in, please give details.

4.
a) In any particular row can there be multiple cells with these colours?
b) In any particular column can there be multiple cells with these colours?

 

[BRB1983]

Conditional formatting yes.
i use this in construction, Document control. on master line list i have in column "B" drawing names. Range "c2:z2000" i have given flange numbers for each flange per drawing.(1000-4500). so not every cell in range ("C2:Z2000") is used. as a flange make-up in field gets complete, i print report and await signatures. Colors: flange number or "Cell" with no color means flange make-up is incomplete, Blue= flange reports are made, but need signature. Yellow = flange reports are finalized. Colors are done by conditional formatting using match.
i need to keep track on how flange numbers are blue and how many are yellow.

 

[Peter_SSs]

Colors are done by conditional formatting using match.

Can you be more specific?
- What is the CF formula for blue in cell C2?
- What is the CF formula for yellow in cell C2?

Apart from changing any relative cell addresses, are those CF formulas the same for the rest of the range C2:Z2000?


Then, what about answers to my other questions 2, 3 and 4 in previous posts?

 

[Fazza]

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