Any equiv. in Conditional Formatting???

[tinfanide]

''' check if each cell in selection does not contain specific words


Dim oCell As Excel.Range
For Each oCell In Selection.Cells

    If InStr(1, oCell.Value, "Teacher", vbTextCompare) = 0 And InStr(1, oCell.Value, "Tutor", vbTextCompare) = 0 Then
        oCell.Interior.ColorIndex = 3
    End If

Next oCell

'''
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Columns("A").Cells) Is Nothing Then
        If InStr(1, Target.Value, "Teacher", vbTextCompare) = 0 And InStr(1, Target.Value, "Tutor", vbTextCompare) = 0 Then
            Target.Interior.ColorIndex = 3
        Else
            Target.Interior.ColorIndex = 0
        End If
    End If
End Sub
'''

The above sets of codes do not auto check Column A every time.


I would prefer Conditional Formatting. But how can I do that like Conditional Formatting?
Can I do it without VBA?

[Colin Legg]

One way - the conditional formatting formula you could use to do this would be:

case insensitive
=COUNT(SEARCH({"teacher","tutor"},A1))=0

case sensitive
=COUNT(FIND({"Teacher","Tutor"},A1))=0

[tinfanide]

Originally Posted by Colin Legg

One way - the conditional formatting formula you could use to do this would be:

case insensitive
=COUNT(SEARCH({"teacher","tutor"},A1))=0

case sensitive
=COUNT(FIND({"Teacher","Tutor"},A1))=0

Thank you for your reply.
Please take a look at the attached file alongside with the questions below:
test.xlsx

Ques 1:
But in Excel 2010, it does not work in the conditional formatting formula as it returns something like "cannot use array in conditional formatting formula".

Ques 2:
In Column B, I want to know why Cell B2 returns a value error but B4 doesn't? Indeed, I think B2 shouldn't have returned a value error.

And
I have come up with a solution in Column E to avoid using array in the formula with the use of "AND".

[Colin Legg]

But in Excel 2010, it does not work in the conditional formatting formula as it returns something like "cannot use array in conditional formatting formula".

Yes, you're right. To work around that you have to use a name. Go to formulas tab > define name. Create a name called Instructors (or whatever you want to call it) which refers to ={"teacher","tutor"}

Then your conditional formatting rule becomes:
=COUNT(SEARCH(Instructors,$A1))=0

Ques 2:
In Column B, I want to know why Cell B2 returns a value error but B4 doesn't? Indeed, I think B2 shouldn't have returned a value error.

In the workbook you attached these cells just have #VALUE! errors - no formulas. So I can't see the original formula / answer this question.

[tinfanide]

Originally Posted by Colin Legg

Yes, you're right. To work around that you have to use a name. Go to formulas tab > define name. Create a name called Instructors (or whatever you want to call it) which refers to ={"teacher","tutor"}

Then your conditional formatting rule becomes:
=COUNT(SEARCH(Instructors,$A1))=0



In the workbook you attached these cells just have #VALUE! errors - no formulas. So I can't see the original formula / answer this question.

Yes, you're right.
I should give a name to the array in 2010.
And you're very specialised in using Excel coz you can tell the little difference between 07 and 10.

And sorry. I don't know why the formula causing the error disappears. I cannot retrieve the formula though. Have to forget them.