Forum Discussion
waygar
Feb 15, 2023Brass Contributor
Speeding Up VBA execution
Hi, The following code searches a large database for cells with a certain fill color and then removes the underlying conditional formatting. The runtime is quite slow. Can this code be made more eff...
tanayprasad
Jul 25, 2023Brass Contributor
You can use the Find method to search for cells with the specific fill color, which can significantly improve the speed.
Sub RemoveConditionalFormattingByColor()
Dim RGBCode As Long
Dim FoundCell As Range
Dim FirstAddress As String
RGBCode = RGB(192, 0, 0)
' Search for the first cell with the specified fill color
Set FoundCell = Columns("D:D").Find(What:=RGBCode, LookIn:=xlFormulas, LookAt:=xlWhole, SearchFormat:=True)
' If a cell with the specified fill color is found, start the loop
If Not FoundCell Is Nothing Then
FirstAddress = FoundCell.Address
Do
' Remove the conditional formatting for the current cell
FoundCell.FormatConditions.Delete
' Find the next cell with the specified fill color
Set FoundCell = Columns("D:D").FindNext(FoundCell)
' Continue the loop until all cells with the specified fill color are found
Loop While Not FoundCell Is Nothing And FoundCell.Address <> FirstAddress
End If
End Sub
Try using this code once.
Best Regards.