|
发表于 2016-11-30 15:48
|
显示全部楼层
本楼为最佳答案
不用条件格式,代码比较烦琐一点。运行4.3秒。
- Sub 填充颜色1()
- Dim BldRng$, Rng5$, Rng4$, Rng3$
- t = Timer
- Range("G6").CurrentRegion.ClearFormats
- arr = Range("G1:g" & Cells(Rows.Count, 7).End(3).Row)
- For i = 1 To UBound(arr)
- x = arr(i, 1): xstr = "," & "G" & i
- If VarType(x) = vbString Then
- If Len(BldRng) + Len(xstr) <= 256 Then 'Range(x)中,x的长度不能超过255
- BldRng = BldRng & xstr
- If Len(x) = 5 Then
- Rng5 = Rng5 & xstr
- ElseIf Len(x) = 4 Then
- Rng4 = Rng4 & xstr
- Else
- Rng3 = Rng3 & xstr
- End If
- Else
- BldRng = Mid(BldRng, 2)
- Range(BldRng).Font.Bold = True
-
- Rng5 = Mid(Rng5, 2)
- If Len(Rng5) Then
- Range(Rng5).Font.Color = RGB(255, 255, 255)
- Range(Rng5).Interior.Color = RGB(0, 0, 255)
- End If
-
- Rng4 = Mid(Rng4, 2)
- If Len(Rng4) Then
- Range(Rng4).Font.Color = RGB(0, 0, 0)
- Range(Rng4).Interior.Color = RGB(0, 255, 0)
- End If
-
- Rng3 = Mid(Rng3, 2)
- If Len(Rng3) Then
- Range(Rng3).Font.Color = RGB(255, 255, 255)
- Range(Rng3).Interior.Color = RGB(255, 0, 0)
- End If
-
- BldRng = xstr
- Rng5 = xstr
- Rng4 = xstr
- Rng3 = xstr
- End If
- End If
- Next
-
- BldRng = Mid(BldRng, 2)
- If Len(BldRng) Then Range(BldRng).Font.Bold = True
-
- Rng5 = Mid(Rng5, 2)
- If Len(Rng5) Then
- Range(Rng5).Font.Color = RGB(255, 255, 255)
- Range(Rng5).Interior.Color = RGB(0, 0, 255)
- End If
-
- Rng4 = Mid(Rng4, 2)
- If Len(Rng4) Then
- Range(Rng4).Font.Color = RGB(0, 0, 0)
- Range(Rng4).Interior.Color = RGB(0, 255, 0)
- End If
-
- Rng3 = Mid(Rng3, 2)
- If Len(Rng3) Then
- Range(Rng3).Font.Color = RGB(255, 255, 255)
- Range(Rng3).Interior.Color = RGB(255, 0, 0)
- End If
-
- Range("H1") = "耗时" & Timer - t & "秒"
- End Sub
复制代码 |
|