0

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
Improve this question
10
  • can you post some test data. I have a feeling you need to do an and instead of an or can you post an image of your workbook , maybe? This could be data related but is hard to tell. Commented Feb 19, 2019 at 21:19
  • 3
    Or Cells(i, 8) <> "" will enter the first condition given any non-empty cell. Commented Feb 19, 2019 at 21:30
  • 1
    So how is the code "intended to work"? There's obviously a business rule here, but without knowing it, it's going to be difficult to untangle the condition. Commented Feb 19, 2019 at 21:35
  • 1
    Not to mention, if you have Cells(i, 8) <> "", that's making the first two criteria for "Cancelled Not Applicable" Or "Completed" completely irrelevant, because it's saying "if a cell is not blank, then do something" Commented Feb 19, 2019 at 21:43
  • 1
    I have this undying hunch that explaining how logical operators and truth tables work would be more beneficial than providing you with code that implements your requirements correctly. Commented Feb 19, 2019 at 22:08

1 Answer 1

Reset to default
2 
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
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

About point 2: I like explaining the Or logical operation as an addition, and And as a multiplication -- visualizing True as 1 and False as 0, the truth tables become pretty easy to understand then.
@MathieuGuindon I know I've stumbled on some good explainations of this NOT OR NOT gate before and I'm having problems recalling it (or finding it). OR is pretty easy to grasp, but NOT OR NOT as it is here is confusing since it's differs so much with the english "If my chicken is not orange or my chicken is not green" which would not allow a orange chicken, obviously. But as a logic gate it would allow it as an orange chicken is clearly not green.
It's like WHERE NOT LIKE ALL (1,2,3) vs WHERE NOT LIKE ANY (1,2,3) in SQL. I've lost some hair learning that one.
Seems parentheses would help clarifying the order of operations in the final expression there - or better, extracting each individual condition into a local variable. "Not empty and equals something" reads very much as still redundant ;-)
@JNevill your explanation was fantastic. code aside, thank you for that.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.