excel VBV填充颜色,并加快代码运行速度

发布网友发布时间：2022-05-01 21:21

共2个回答

热心网友时间：2022-06-23 16:49

k = Cells(rC.Row, 3) 不知道你这个C列的列会不会变化，你没说，我就默认为不变化。

Sub 对比字符填色1()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim rC, rT As Range
Dim i, j As Integer, k As String
For c = 20 To 89 Step 3
Set rT = Sheets("填色").Range(Cells(10, c), Cells(2000, c))
For Each rC In rT
    If rC = "" Then Exit For
    k = Cells(rC.Row, 3)
    If k <> "" Then
        For i = 1 To Len(rC)
            If InStr(k, Mid(rC, i, 1)) > 0 Then
                rC.Characters(i).Font.ColorIndex = 3
            Else
                rC.Characters(i).Font.ColorIndex = -4105
            End If
        Next i
    End If
Next rT
Next c
End Sub

追问

高速度最好，现在代码出现，见补充图片。20个表每个表2000行，高速度最好，高速度最好。

热心网友时间：2022-06-23 16:50

高速度
Sub 对比字符填1()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim rC, rT As Range
Dim i, j As Integer, k As String
For c = 20 To 89 Step 3
Set rT = Sheets("sheet1").Range(Cells(10, c), Cells(2000, c))
For Each rC In rT
If rC = "" Then Exit For
k = Cells(rC.Row, 3)
l = Len(k)
If k <> "" Then
For i = 1 To Len(rC) Step l '高速度关键1
If InStr(k, Mid(rC, i, 1)) > 0 Then rC.Characters(i, l).Font.ColorIndex = 3 '高速度关键2
Next i
End If
Next
Next
End Sub