CODEWITHSUNDEEP
excelvbaif-statementconditional-statements
VBAWARD
VBAWARD

Reputation: 71

multiple IF statement conditions using OR function doesn't work - VBA

I'm trying to get the code to check if multiple conditions are true and if so, to then change the colour of the cell but this doesn't work and highlights way more than necessary. I'm not sure why the code doesn't work as intended:

Sub ConditionCheck()

Dim wb As Workbook
Dim ws As Worksheet
Dim lastrow As Long
Dim rng1 As Range
Dim rng2 As Range
Dim c As Range
Dim d As Range

Set wb = ActiveWorkbook
Set ws = wb.Sheets("Source")
lastrow = Sheets("Source").Cells(Rows.Count, "A").End(xlUp).Row

i = 2

For i = 2 To lastrow

    If Cells(i, 8) = "Cancelled Not Applicable" Or Cells(i, 8) = "Completed" Or Cells(i, 8) <> "" Then
        If Cells(i, 23) <> "Cancelled" Or Cells(i, 23) <> "Completed" Then
            Cells(i, 23).Interior.ColorIndex = 4
        End If
    End If
Next i


End Sub

Sample Data:

CASE ID       CASE STATUS                PROGRAM STATUS
10001         Active                     Pending
10002         Completed                  Pending Review
10004         Cancelled Not Applicable   Cancelled

Upvotes: 0

Views: 2158

Answers (1)

JNevill
JNevill

Reputation: 50019

If Cells(i, 8) = "Cancelled Not Applicable" Or Cells(i, 8) = "Completed" Or Cells(i, 8) <> "" Then
    If Cells(i, 23) <> "Cancelled" Or Cells(i, 23) <> "Completed" Then
        Cells(i, 23).Interior.ColorIndex = 4
    End If
End If

This says:

If Your case status is "Cancelled Not Applicable" or you case status is "Completed" or your case status is any value (not empty) then go ahead and test if your program status is any value (as it can't be both "Cancelled" AND "Completed" at the same time) then go ahead and highlight it.

So really there are two issues here.

  1. Your Or Cells(i, 8).value <> "" Here you are saying "Pass this IF line as true if this cell is blank" because it's an OR. It's like saying "If this chicken is red or this chicken is black or this chicken exists" So if you are testing a blue chicken it will pass as it exists.

  2. Your Cells(i, 23) <> "Cancelled" Or Cells(i, 23) <> "Completed". If the cell contains "Cancelled" then your second condition is true since it doesn't say "Completed" so this passes. If your cell says "Completed" then your first condition passes as it's not "Cancelled". If your cell says "Booger farts" then both conditions pass as it's not "Cancelled" nor is it "Completed". So really you want AND here.

That second point is difficult to grasp since "OR" isn't how we would use it english. It may help to think of it this way: If we have a test like IF condition1 OR condition2 OR condition3 then only one of those conditions needs to be true for this to pass. If TRUE OR FALSE OR FALSE passes. Your conditions here are "Not equal to" which adds to the confusion, but you merely have to concentrate on the individual condition and determine if it returns "TRUE" or "FALSE" and make your statement IF TRUE OR FALSE (passes) or IF TRUE OR TRUE (passes) or IF FALSE OR TRUE(passes) or IF FALSE OR FALSE (finally it fails!).

Instead you wish to say:

If the CASE STATUS is filled in and if the PROGRAM STATUS is IN PROGRESS or PENDING or PENDING REVIEW, etc. then the CASE STATUS cannot be CANCELLED OR COMPLETED

So:

If Cells(i,8).value <> "" AND (Cells(i, 8).value = "Cancelled Not Applicable" OR Cells(i,8).value = "Completed") AND (Cells(i, 23).Value <> "Cancelled" AND Cells(i, 23).Value <> "Completed") Then
    Cells(i, 23).Interior.ColorIndex = 4
End If

Upvotes: 2

Related Questions