excel 加速范围内的if循环- vba

deyfvvtc  于 2023-08-08  发布在  其他
关注(0)|答案(3)|浏览(117)

我需要VBA代码,将颜色的文本条件时,满足一个单元格。现在我的代码效率不高,可能是因为循环搜索的列E的整个范围。问题是,在E列中,我总是有一个不同范围的已使用单元格。你能帮我加速代码吗?

  1. Sub color_status()
  2. Dim cell As Range
  3. For Each cell In Range("E:E")
  4. If cell.value = "Open" Then
  5. cell.Interior.Color = RGB(198, 224, 180)
  6. ElseIf cell.value = "In Progress" Then
  7. cell.Interior.Color = RGB(169, 208, 142)
  8. ElseIf cell.value = "Done" Then
  9. cell.Interior.Color = RGB(112, 173, 71)
  10. ElseIf cell.value = "On Hold" Then
  11. cell.Interior.Color = RGB(226, 239, 218)
  12. End If
  13. Next cell
  14. End Sub

字符串

7fyelxc5

7fyelxc51#

使用Intersect

  1. Sub color_status()
  2. Dim rng As Range, cel As Range, s As String, dict As Object
  3. Dim t0 As Single: t0 = Timer
  4. Set dict = CreateObject("Scripting.Dictionary")
  5. With dict
  6. .Add "Open", RGB(198, 224, 180)
  7. .Add "In Progress", RGB(169, 208, 142)
  8. .Add "Done", RGB(112, 173, 71)
  9. .Add "On Hold", RGB(226, 239, 218)
  10. End With
  11. With ActiveSheet
  12. Set rng = Application.Intersect(.UsedRange, .Range("E:E"))
  13. End With
  14. If rng Is Nothing Then
  15. MsgBox "No data", vbExclamation
  16. Exit Sub
  17. Else
  18. For Each cel In rng
  19. s = Trim(cel.Value)
  20. If dict.exists(s) Then
  21. cel.Interior.Color = dict(s)
  22. End If
  23. Next
  24. End If
  25. MsgBox rng.Rows.Count & " rows scanned", _
  26. vbInformation, Format(Timer - t0, "0.0 secs")
  27. End Sub

字符串

展开查看全部
nuypyhwy

nuypyhwy2#

Range(“E:E:)太大,无法循环-它大约有100万个像元
1 variant使用UsedRange限制它,并通过索引循环:

  1. Dim Ecount as Long, i as Long
  2. Ecount=ActiveSheet.UsedRange.Rows.count
  3. For i=1 to Ecount
  4. if Cells(i,5)= "Open" Then
  5. '(etc. your code inside the loop)
  6. Next i

字符串
或2种变体

  1. Dim lRange as Range
  2. lRange=Range(Cells(1,5),Cells(ActiveSheet.UsedRange.Rows.count,5))
  3. 'then loop as you do with the lRange
  4. For each cell in lRange
  5. '(etc. your code inside the loop)
  6. Next cell


对我来说,使用单元格索引循环是调试更容易的-当你调试时,你立即知道行号

展开查看全部
rmbxnbpk

rmbxnbpk3#

我已经根据您提供的值创建了一个Excel文件,但我使用了条件格式。这就是结果:
x1c 0d1x的数据
由于它不是使用VBA完成的,因此性能是即时的,但我想知道您的RGB值是否正确:-)

相关问题