How to compare sheet cell values for better performance?

[dmadhup]

Hello,

I have developed the following code to compare cell values and color it. I know my code is accessing the sheet every time which makes program slow a for a large number of comparisons.

Is anyone have an idea to optimize the code using array OR any other way to run faster than this? Thank you!

Sub ValueComparision()
Sheets("Sheet1").Select
Dim lastRow As Long, i As Long
lastRow = Cells(Rows.Count, "B").End(xlUp).Row
For i = 14 To lastRow
    If Range("V" & i).Value > Range("U" & i).Value Then
        Range("V" & i).Interior.Color = RGB(255, 150, 77)
    Else
        Range("V" & i).Interior.ColorIndex = 0
    End If
    
    If Range("W" & i).Value > (Range("V" & i).Value Or Range("U" & i).Value) Then
        Range("W" & i).Interior.Color = RGB(255, 99, 71)
    Else
        Range("W" & i).Interior.ColorIndex = 0
    End If
    
    If Range("X" & i).Value > (Range("W" & i).Value Or Range("V" & i).Value Or Range("U" & i).Value) Then
        Range("X" & i).Interior.Color = RGB(255, 99, 71)
    Else
        Range("X" & i).Interior.ColorIndex = 0
    End If
    
    If Range("Y" & i).Value > _
    (Range("X" & i).Value Or Range("W" & i).Value Or Range("V" & i).Value Or Range("U" & i).Value) Then
        Range("Y" & i).Interior.Color = RGB(255, 99, 71)
    Else
        Range("Y" & i).Interior.ColorIndex = 0
    End If
    
    If Range("Z" & i).Value > _
    (Range("Y" & i).Value Or Range("X" & i).Value Or Range("W" & i).Value Or Range("V" & i).Value Or Range("U" & i).Value) Then
        Range("Z" & i).Interior.Color = RGB(255, 99, 71)
    Else
        Range("Z" & i).Interior.ColorIndex = 0
    End If
    
    If Range("AA" & i).Value > _
    (Range("Z" & i).Value Or Range("Y" & i).Value Or Range("X" & i).Value Or Range("W" & i).Value Or Range("V" & i).Value Or Range("U" & i).Value) Then
        Range("AA" & i).Interior.Color = RGB(255, 100, 100)
    Else
        Range("AA" & i).Interior.ColorIndex = 0
    End If
    
    If Range("AB" & i).Value > _
    (Range("AA" & i).Value Or Range("Z" & i).Value Or Range("Y" & i).Value Or Range("X" & i).Value Or Range("W" & i).Value Or Range("V" & i).Value Or Range("U" & i).Value) Then
        Range("AB" & i).Interior.Color = RGB(255, 69, 0)
    Else
        Range("AB" & i).Interior.ColorIndex = 0
    End If
Next i
    
End Sub

 

[Joe4]

Is this something that might be better served with Conditional Formatting instead of VBA?

 

[dmadhup]

Hi Joe4,
Yes, it's possible. But my sheet is controlled by VBA code, therefore i am trying to integrate inside VBA.

 

[Joe4]

Yes, it's possible. But my sheet is controlled by VBA code

I don't believe that should matter. You can use Conditional Formatting, along with whatever VBA that you may have. It isn't an "either-or" proposition, you can have both, as long as they aren't fighting each other.

Some of the advantages of Conditional Formatting is that it all happens automatically. So you don't need to worry about calling it to run, and it is much more efficient that running VBA code with loops.

So if you are really concerned about performance, you many want to look at replacing certain VBA code with Conditional Formatting, where applicable.

 

[dmadhup]

Thank you Joe4.
I will definitely try with conditional formatting to see how it goes.