VBA - problems with cell formatting multiple IF statements

[wjvghost]

Hello,

I am trying to make a custom conditional formatting which does the following:

Check if Columns D and E are equal to each other.

If they are equal, then check if they are between a value of 500 to 2000. If all this is met then the cells turn green.

I have a second part to it which changes everything outside of those constraints to red.

It is changing parts of my numbers to red even though they are within the parameters I set.

I am not sure if I have done something wrong, but for some reason I can't seem to understand it.

Sub condTest()
Dim lastRow As Long
Dim dRange As Range
Dim Cell As Range
    
    With Worksheet
  
        lastRow = Cells(Rows.Count, "D").End(xlUp).Row
        Set dRange = Range("D2:D" & lastRow)

    End With
    If Range("D2").Value = 0 Then
    Exit Sub
    End If

    For Each Cell In dRange
        Cell.Activate
            If Cell.Value <> Cell.Offset(0, 1) Then
                Cell.Style = "Bad"
                Cell.Offset(0, 1).Style = "Bad"
            End If
            
            If Cell.Value < 500 Or Cell.Value > 2000 And Cell.Value = Cell.Offset(0, 1).Value Then
                Cell.Style = "Bad"
                Cell.Offset(0, 1).Style = "Bad"
            End If
    Next Cell
        
    Range("A1").Select
    ActiveWindow.SmallScroll Up:=1048576
End Sub

The part which turns the cells green saying they are "ok" works fine, but this part which changes the style to "Bad" if it is not correct is overwriting the green formatting and I am not sure why.

I have been staring at this for a while, so it may be glaring me in the face but I can't seem to figure it out.

/edit: these values are being returned from a GetDetailsOf object from another macro, if that makes any difference at all.

 

[Joe4]

You are welcome.