Microsoft Office Forums - View Single Post - [Solved] Any equiv. in Conditional Formatting???

tinfanide · 06-02-2012, 03:44 AM

Code:

''' 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?