Excel VBA: Conditional Formatting - Interior Colors

Question

I am trying to get some conditional formatting working in VBA for Excel 2013. The execution of the code should have the cell (interior color) in Col N turn Green if the list validation is "Complete" and white any other time. The cell (interior color) in Col O should turn Red if the list validation in Col N is "Held" and white any other time.

Currently, the results that appear are: 1. Col N and Col O are white if nothing is selected from the list validations. 2. Col N turns green when anything is selected from the list validations. 3. Col O turns red when "Held" is selected in Col N and turns white again if anything else in Col N is selected.
4. If something is selected in Col O, then the cell turns red.

My current code is (along with sections that I've commented out):

'Add conditional format for column N.  If Status is "Complete", color Status cell (col N) green (43).

With Worksheets(SheetNum & " - Work").Range("N2:N2000").Select
    'Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
        Formula1:="=OR(($N2=""Not Started""),($N2=""In Queue""), ($N2=""In Work""), ($N2=""Held""), ($N2="" "")"
    'Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlNotEqual, _
        Formula1:="=($N2=""Complete"")"
    'With Selection.FormatConditions(1)
        '.Interior.ColorIndex = 2
        '.StopIfTrue = True
    'End With
'End With

    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlNotEqual, _
        Formula1:="=($N2=""Complete"")"
    With Selection.FormatConditions(1)
        .Interior.ColorIndex = 43
        '.StopIfTrue = True
    End With
End With

'Add conditional format for column O.  If Status is "Held", color Held For cell (col O)
'red (3).
'With Worksheets(SheetNum & " - Work").Range("O2:O2000").Select
    'Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlNotEqual, _
        Formula1:="=OR(($N2=""Not Started""),($N2=""In Queue""), ($N2=""In Work""), ($N2=""Complete""))"
    'Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
        Formula1:="=($N2=""Held"")"
    'With Selection.FormatConditions(1)
        '.Interior.ColorIndex = 2
        '.StopIfTrue = True
    'End With

    With Worksheets(SheetNum & " - Work").Range("O2:O2000").Select
        Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlNotEqual, _
            Formula1:="=($N2=""Held"")"
        With Selection.FormatConditions(1)
            .Interior.ColorIndex = 3
            '.StopIfTrue = True
        End With
    End With

Also, can someone explain when to use Operator:=xlNotEqual vs Operator:=xlEqual? These seem to be working in reverse of what I would have expected.

Thanks for any help.

Answer 1

Unfortunately, these columns need to be colored on the fly which is why I'm trying to use conditional formatting. There are about 10 conditional formats that I'm trying to apply to these sheets which will be created throughout the life of the program.

On the plus side, after trying things all day, I noticed I was missing a ")" after (""Held""). It doesn't seem to have made the file work correctly, though.

Answer 2

Does the cell $N2 contain the actual words "Complete" and "Held" ?

If so maybe this will work

Sub tester()
    For I = 1 To 100 ' or lastused row
        If InStr(1, UCase(Sheet6.Range("N" & I).Value), "COMPLETE") > 0 Then
            Sheet6.Range("N" & I).Interior.ColorIndex = 43
        Else
            If InStr(1, UCase(Sheet6.Range("N" & I).Value), "HELD") > 0 Then
                Sheet6.Range("N" & I).Interior.ColorIndex = 3
            Else

                Sheet6.Range("N" & I).Interior.ColorIndex = 2
            End If
        End If

    Next I
End Sub

Answer 3

I have finally figured out the correct code to get what I want done.

    'Add conditional format for column N.  If Status is "Complete", color Status cell (col N) green (43).
With Worksheets(SheetNum & " - Work").Range("N2:N2000").Select
        Selection.FormatConditions.Add Type:=xlExpression, _
            Formula1:="=($N2=""Complete"")"
        With Selection.FormatConditions(1)
            .Interior.ColorIndex = 43
            .StopIfTrue = True
        End With
    End With