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

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
No, a cell can only have one colour, but the cell could meet 2 or more of the CF rules. If the cell meets 2 or more CF conditions, the format of the last one that is true will be applied unless an earlier condition has 'Stop if True' set.

A) I probably should have been more clear, B) I could have just done this myself before I asked my question, but I was running out and asking was just easier...

I took a cell and formatted the interior color to a shade of gray, then I put a CF on the cell to turn it blue. I can now get to this cell by using two different colors.

Code:
Sub test()
    Dim rgb As Long, rgb2 As Long, ct As Long
    If Range("A1").Interior.Color = 12566463 Then ct = ct + 1
    If Range("A1").DisplayFormat.Interior.Color = 13995347 Then ct = ct + 1
    MsgBox "Cell A1 has " & ct & " colors"
End Sub

I don't know what this gets me but I do find it interesting.

Thank you for all your time.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,539
Messages
6,114,221
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