Highlight rows based pf column criteria VBA

Question

Im trying to write a VBA script to compare two = rows and have the spreadsheet highlight the duplicate rows only if certain criteria is met, such as (Value of row, column a = Value of row-1, column) AND Value of row, column b > Value of row-1, column b) Then entirerow of the greater value in column b.font.color = vbRed.

Here is a section of the table I'm running...

Table Selection

Here is the code I am using...

Sub RemoveDuplicates()

Dim i As Long, R As Long
'Dim DeviceName As Range, SerialNumber As Range, LastContact As Range

Application.ScreenUpdating = False

R = Cells(Rows.Count, 1).End(xlUp).Row

'Set DeviceName = Columns(2)
'Set SerialNumber = Columns(3)
'Set LastContact = Columns(7)

For i = R To 2 Step -1

'If Cells(i, "F").Value > Cells(i - 1, "F").Value Then
'Code above doesn't work

If Cells(i, 3).Value = Cells(i - 1, 3).Value And Cells(i, 2).Value = Cells(i - 1, 2).Value Then

    'If Cells(i, 3).Value = Cells(i - 1, 3).Value And Cells(i, 2).Value = Cells(i - 1, 2).Value And Cells(i, 5).Value > Cells(i - 1, 5).Value Then
    'Code above doesn't work

    Cells(i, 1).EntireRow.Font.Color = vbRed
    End If
Next i

Application.ScreenUpdating = True

End Sub

I can get the duplicates to highlight, but when I try to introduce the greater than check, the system gets janky.

Your code contradicts itself and the narrative in terms of what columns are to be used for the conditions. Perhaps it would be best to simply spell it out word-for-word what you are trying to achieve. — user4039065

Unknown Unknown · Accepted Answer · 2018-07-03T17:45:27

try a conditional formatting rule.

With worksheets("sheet1").usedrange.offset(1, 0).entirerow
    .FormatConditions.Delete
    With .FormatConditions.Add(Type:=xlExpression, Formula1:="=and($a2=$a1, $b2=$b1, $f2>$f1)")
        .font.Color = vbRed
    End With
End With

Highlight rows based pf column criteria VBA

1 Answers