I need a code that will test for the presence of the word "recovered" in an offset range of cells (Column T) and clear the original target cell contents (Column AD) , but only if "recovered" is NOT preceded by a "no" or "not". I never know whether the words will occur upper, lower, or proper case.
The code I have below doesn't work consistently.
Dim cel As Range
For Each cel In Range("AD6:AD469")
If InStr(LCase(cel.Offset(0, -10)), "not recover") Or InStr(LCase(cel.Offset(0, -10)), "no recover") = 0 And InStr(LCase(cel.Offset(0, -10)), "recover") > 0 Then
cel.Interior.ColorIndex = 40
Else: End If