Faster way to loop

BRB1983

Board Regular
Joined
Aug 29, 2019
Messages
61
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.
Code:
  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
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
This should be slightly faster.
Code:
    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
 
Upvote 0
This should be even quicker...

Code:
    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
 
Last edited:
Upvote 0
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?
 
Upvote 0
@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
 
Upvote 0
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?
 
Last edited:
Upvote 0
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.
 
Upvote 0
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?
 
Last edited:
Upvote 0
If supported by your Excel version, instead of looping, have a look at using autofilter by color & then count the visible cells.
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

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
Back
Top