"if not instr" doesn't work as i thought it should in vba

Question

I have a sheet with a list of contracts with a column for the supplier name and one for the ending date, what I want to do is to make all passed dates red unless the contract is from a specific supplier. I had previously used InStr to check if a cell had a string inside so since I have just started with vba I thought of inserting the following in my loop

If Not InStr(LCase(Data.Cells(i, 4).Value), "flowserve") Then
    If CDate(Data.Cells(i, "h").Value) < Date Then _
        Data.Cells(i, "h").Font.Color = -16776961
End If

expecting it would colour passed dates of the contracts that do not have "flowserve" in the supplier name. But instead it just seems to colour all the passed dates as if the InStr was not finding anything anywhere.

I then tried to do

If InStr(LCase(Data.Cells(i, 4).Value), "flowserve") Then
    'do nothing
Else
    If CDate(Data.Cells(i, "h").Value) < Date Then _
        Data.Cells(i, "h").Font.Color = -16776961
End If

and this works as I wanted, so i'm guessing that the Not does not work as I thought.

Now even if I kind of have a solution I was hoping that someone could explain what went wrong in the first code. Hope I was clear enough.

thanks in advance everybody!

Answer 1

https://msdn.microsoft.com/en-us/library/8460tsh1%28v=vs.90%29.aspx

InStr returns an integer not a boolean, try comparing it against the value it returns.

If InStr(LCase(Data.Cells(i, 4).Value), "flowserve") = 0 Then
    If CDate(Data.Cells(i, "h").Value) < Date Then _
        Data.Cells(i, "h").Font.Color = -16776961
End If

Answer 2

you must be very careful using Not with Integer value. See the following examples:

Sub InStrTest()

Dim DataCellsTrue As String
Dim DataCellsFalse As String

DataCellsTrue = "flowserve"
DataCellsFalse = "haha"

Debug.Print InStr(LCase(DataCellsTrue), "flowserve") 'return 1
Debug.Print Not InStr(LCase(DataCellsTrue), "flowserve") 'return -2
Debug.Print CBool(InStr(LCase(DataCellsTrue), "flowserve")) 'return true
Debug.Print Not CBool(InStr(LCase(DataCellsTrue), "flowserve")) 'return false
Debug.Print InStr(LCase(DataCellsFalse), "flowserve") 'return 0
Debug.Print Not InStr(LCase(DataCellsFalse), "flowserve") 'return -1
Debug.Print CBool(InStr(LCase(DataCellsFalse), "flowserve")) 'return false
Debug.Print Not CBool(InStr(LCase(DataCellsFalse), "flowserve")) 'return true

End Sub