Removing Specific Fill Colors
Hi,
I would like to ask for any advice on how to make my VBA code run faster. What I'm doing is, I have a list of workbooks that I need to check if each worksheet inside it contains the target fill colors that I'm looking. If a target fill color is seen in the current worksheet, I remove the fill color.
I already set Application.ScreenUpdating to False and minimize the usage of .Select(), but it is still slow.
My code is something like this. This function is being called by another function, since I need to check each sheet in the current workbook
Code:
Public Function removeFillColors(ws as Worksheet)Dim searchKey as Range
Dim cellRange as Range
' This looks at the sheet for a specific keyword
' If the keyword is found, proceed to the removing of fill colors
Set searchKey = GetSearchKey(ws)
If not searchKey Is Nothing Then
'Loop each cell in UsedRange
For each cellRange In ws.UsedRange
'If the cell's fill color exist in my dictionary of target fill colors
'Remove the fill color of the current cell
If dictColors.Exists(cellRange.Interior.Color) Then
cellRange.Interior.Color = xlNone
End If
next cellRange
End IF
End Function
If my list contains only one file, it takes about 16 seconds. But if I add another file, it really takes time that I'm forced to stop it. :(
Would really appreciate if someone can give me an advice on how to solve my problem.