VBA - problems with cell formatting multiple IF statements

wjvghost

New Member
Joined
Jan 20, 2017
Messages
41
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.

Code:
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.
 
Last edited:

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Forum statistics

Threads
1,223,908
Messages
6,175,304
Members
452,633
Latest member
DougMo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top