时间戳

首页 > 解决方案 > 有没有更快的方法在 excel vba 中循环使用 ColorCounter？

excel - 有没有更快的方法在 excel vba 中循环使用 ColorCounter？

问题描述

我有一个可以工作的 lColorCounter 代码，但速度很慢。有没有更快的方法来循环使用 lColorCounter？

  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

标签： excelvbaperformanceloopscounter

解决方案

循环一次：

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

推荐阅读