VBA Replace using inequality

user_11703150 0
Viewed 102

I have a frequent data process for a client that has to be Excel VBA. It's 3000x60 grid of cells on 6 tabs, so it can take a while to loop. The test task is to find values >5 and highlight them.

Conditional formatting is not an option for various reasons.

A for loop is obvious, and I'm open to that, but I'm looking for a speed improvement.

Replace seems faster. I want to try Selection.Replace What:=">5" but that looks for the string not the inequality I really want.

vba excel loops replace performance
2 Answers

Maybe this is an interesting approuch?

Sub Highlight()

Dim WS As Worksheet

For Each WS In ThisWorkbook.Worksheets
    For X = 1 To 60
        On Error Resume Next
        With Intersect(WS.UsedRange, WS.Columns(X))
            .AutoFilter 1, ">5"
            .Offset(1).Resize(.Rows.Count - 1).Interior.Color = vbYellow
            .AutoFilter
        End With
        On Error GoTo 0
    Next X
Next WS

End Sub

Obviously there is plenty of room for improvement (turn of screenupdating and calculation for example) but you should get the idea. I have no clue if it is faster :). At least you won't be looping all cells.

enter image description here

enter image description here

user_97581940

Try:

Sub Highlight()
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Dim row As Long, col As Long, ws As Variant
    For Each ws In Worksheets
    For row = 1 To 3000
    For col = 1 To 60
        If ws.Cells(row, col).Value > 5 Then ws.Cells(row, col).Interior.ColorIndex = 4 'light green
    Next
    Next
    Next
    Application.ScreenUpdating = True
    Application.EnableEvents = True
End Sub
user_71325500
Related