Compare Two Values in different columns-CodePudding

I have this worksheet and i need to compare the "venda" values with "esperado". If Vendas > Esperado i need to paint the status cell with green, if Vendas < Esperado it will be red

Is there a way to make this process entirely on vba? I am still learning this tool

CodePudding user response：

Please, use the next code. It will create two Union ranges (for each cell interior color type), according to the required condition and color their interior at the code end, at once. That's why it will be fast enough for reasonable ranges number of rows. If huge ranges, I can supply a different solution:

Sub PaintCells()
 Dim sh As Worksheet, lastR As Long, arr, rngGreen As Range, rngRed As Range, i As Long
 
 Set sh = ActiveSheet
 lastR = sh.Range("A" & sh.rows.count).End(xlUp).row
 arr = sh.Range("B2:C" & lastR).Value2
 
 For i = 1 To UBound(arr)
    If arr(i, 1) > arr(i, 2) Then
            addURange rngGreen, sh.Range("D" & i   1)
    ElseIf arr(i, 1) < arr(i, 2) Then
            addURange rngRed, sh.Range("D" & i   1)
    End If
 Next i
 If Not rngGreen Is Nothing Then rngGreen.Interior.Color = vbGreen
 If Not rngRed Is Nothing Then rngRed.Interior.Color = vbRed
End Sub

CodePudding user response：

Sub ValiaFuncionario()

Dim tables As Range

Set table = Range("B8", Range("B8").End(xlToRight).End(xlDown))

For Each Row In table.Rows

If Row.Cells(1, 2).Value < Row.Cells(1, 3) Then
    Row.Cells(1, 4).Interior.Color = vbRed
    Row.Cells(1, 4).Value = "ABAIXO"
Else
    Row.Cells(1, 4).Interior.Color = vbGreen
    Row.Cells(1, 4).Value = "ACIMA"
End If

Next Row

End Sub

i did like this and it worked